Measure in Dax with different filters

[CristinaLia]

Hi Everyone,

I am new to DAX so I've been struggling with this for a bit. In my report I use the measure below, and it does what I expect it to.

UCN Count CY = CALCULATE(
		Trigger[UCN Count],
		FILTER(Trigger,Trigger[TriggerDate].[Year]=year(now())),
		FILTER(Trigger,Trigger[CurrentInd]=True))

Nevertheless, I realised that for 'Trigger Dates' that are in the past, I don't need to use the second filter (ie FILTER(Trigger,Trigger[CurrentInd]=True)

So basically, I want to change this measure to use   Option 1 for any date from today till the end of the year and Option 2 for dates that are this year, but in the past(so from beginning of the year till today). Ideally, I need this in one measure but I;m not sure how to write it to get to the result I want.

(FYI UCN Count is a measure, TriggerDate is a date field from my data and CurrentInd is a true/false field from my data)

OPTION 1
CALCULATE(
		Trigger[UCN Count],
		FILTER(Trigger,Trigger[TriggerDate].[Year]=year(now())),
		FILTER(Trigger,Trigger[CurrentInd]=True))

OPTION 2
CALCULATE(
 Trigger[UCN Count],
 FILTER(Trigger,Trigger[TriggerDate].[Year]=year(now())))

I'm not sure if this is enough information, please let me know if you need more on this.

Thanks,
Lia

[Greg_Deckler]

Perhaps something like:

UCN Count CY = IF(<some condition>, 

CALCULATE(
		Trigger[UCN Count],
		FILTER(Trigger,Trigger[TriggerDate].[Year]=year(now())),
		FILTER(Trigger,Trigger[CurrentInd]=True)),

CALCULATE(
		Trigger[UCN Count],
		FILTER(Trigger,Trigger[TriggerDate].[Year]=year(now())))

?

[CristinaLia]

Hi Smoupre,

Thank you very much for this, but I'm not sure how to get this right. What you are proposing is actually one of the things I've tried, I used something like IF (Trigger[TriggerDate] > now()) but it doesn't work as TriggerDate is a column, it's not a measure.

Any idea how I can get around that? I thought about creating another measure that I can then reference in that IF, but don;t know how

[Greg_Deckler · Accepted solution]

Would have to see some sample/example data. But, generally you just use an aggregator like MAX or MIN with a column in a measure. As long as the visualization adds the correct filter context then you should be good.

[CristinaLia]

Hi,

The IF I had in mind works perfectly with adding a MAX on the column I was looking at. It's perfect, thank you very much for your help, I can;t believe I didn;t think of using it before.

Thanks again

[Greg_Deckler]

Awesome, sometimes you just need a second pair of eyes!