Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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())))
?
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
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.
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
Awesome, sometimes you just need a second pair of eyes!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |