July 13, 2015 at 7:01 am
Hi all,
I have a measure that is a running total in a selected Month, as shown below;
CumulativeCount:=TOTALMTD(COUNTA('ConsolidatedFiles'[DiligentaCode]),'- EffectiveAccountingDate'[DateKey])
I need to add a condition on this based on another column (named AdminSystem). I basically want the following;
IF AdminSystem = 'TB'
THEN TOTALMTD(COUNTA('ConsolidatedFiles'[DiligentaCode]),'- EffectiveAccountingDate'[DateKey])
ELSE COUNTA('ConsolidatedFiles'[DiligentaCode])
Is there any way this can be done in DAX?
Thanks in advance!
July 13, 2015 at 9:05 am
Quick and dirty way
Create a new computed calculated column in your fact table based on the condition you have specified. Now create any new measure based on the newly created column in the fact table
Raunak J
July 13, 2015 at 10:25 am
What would I put in the computed column? I can't put the TOTALMTD function can I?
July 13, 2015 at 10:34 am
Did you look into creating a Calculated Member?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 14, 2015 at 5:09 am
I don't know what to put in the Calculated Member though?
July 14, 2015 at 5:42 am
This isn't quite there but may help you to get closer. I'm new to DAX also. My idea was to add an additional filter to your calculate statement with an or condition. I have a calculated column that is very similar but the columns are in the same table in my case.
=CALCULATE(COUNTA('ConsolidatedFiles'[DiligentaCode]), [AdminSystem] = "TB" || '- EffectiveAccountingDate'[DateKey] = '- EffectiveAccountingDate'[DateKey], DATESMTD('- EffectiveAccountingDate'[DateKey]))
This part of my statement is incorrect:
[AdminSystem] = "TB" || '- EffectiveAccountingDate'[DateKey] = '- EffectiveAccountingDate'[DateKey]
I think you could build a Filter() on this. Someone with more experience may be able to help.
July 14, 2015 at 6:38 am
Thanks for that, but I'm struggling to get it to work!
I've tried to use this SWITCH() statement..
CountMeasure:=SWITCH (TRUE (),FIRSTNONBLANK ( 'ConsolidatedFiles'[AdminSystem], TRUE () ) = "TB", CountA ( [DiligentaCode] ),FIRSTNONBLANK ('ConsolidatedFiles'[AdminSystem], TRUE () )<> "TB", TOTALMTD(COUNTA('ConsolidatedFiles'[DiligentaCode]), '- EffectiveAccountingDate'[DateKey]))
This works when there is a 'TB' record for a selected date, however, when there isn't the total count of rows up to that date is returned.
So for example, this is what my table would look like..
AdminSystem DiligentaCodeDate
TB ABC12301/01/2015
TB ABC12401/01/2015
TB ABC12502/01/2015
AA ABC12601/01/2015
AA ABC12702/01/2015
AA ABC12803/01/2015
AA ABC12903/01/2015
AA ABC13003/01/2015
TB ABC13104/01/2015
I'd put the Admin System in Rows in a pivot table, an have the Date as a filter/slicer. I'd then create my measure by counting the DiligentaCode column. I want the measure to work as follows;
- If I filter the date to 01/01/2015, I want to return a count of 2 for TB and a count of 1 for AA.
- If I filter the date to 02/01/2015, I want to return a count of 1 for TB and a count of 2 for AA (which is cumulative for the month)
- If I filter the date to 03/01/2015, I want to return nothing for TB as a row doesn't exist for this date, and a count of 5 for AA (cumulative)
- If I filter the date to 04/01/2015, I want to return a count of 1 for TB and a count of 5 for AA
I hope this makes sense! I feel like I'm so close, however I need to make sure the TB count is related to the date selected, whereas all the other types are cumulative for the month.
July 15, 2015 at 5:47 am
Anyone?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply