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.
The measure below works fine below until I select the year in a slicer. The years in my app are 2014 - 2017 and the expression below correctly assumes max year -1 (2016) when nothing is selected, but when I select 2017 it goes blank.
The goal is if I select 2017 in the slicer then expression below counts for year 2016 and so on...
CountHeadcountPriorYear =
calculate(COUNTROWS(LnkAssociatesPeriod),FILTER(LnkAssociatesPeriod,LnkAssociatesPeriod[Period_Year]=Max(LnkAssociatesPeriod[Period_Year])-1),LnkAssociatesPeriod[AssociatesATSkey])
Solved! Go to Solution.
@Anonymous,
I create two new measures using the following DAX , both work well. Could you please post a screenshot about your scenario and share sample data of your table here?
CountHeadcountPriorYear = calculate(COUNTROWS(LnkAssociatesPeriod),FILTER(ALL(LnkAssociatesPeriod),LnkAssociatesPeriod[Period_Year]=Max(LnkAssociatesPeriod[Period_Year])-1),LnkAssociatesPeriod[AssociatesATSkey])
CountHeadcountPriorYear =VAR targetyear = Max(LnkAssociatesPeriod[Period_Year])-1 RETURN CALCULATE(COUNTROWS(LnkAssociatesPeriod),FILTER(ALL(LnkAssociatesPeriod),LnkAssociatesPeriod[Period_Year]=targetyear),LnkAssociatesPeriod[AssociatesATSkey])
Regards,
Lydia
If you select a year in the slicer, the entire table is filtered to that year. That year - 1 is excluded by your slicer selection. You would need to remove the filter context from the measure using ALL.
CountHeadcountPriorYear =
calculate(COUNTROWS(LnkAssociatesPeriod),FILTER(ALL(LnkAssociatesPeriod),LnkAssociatesPeriod[Period_Year]=Max(LnkAssociatesPeriod[Period_Year])-1),LnkAssociatesPeriod[AssociatesATSkey])
...should work, but to be safe I would use a variable to keep the slicer selection separate from your filter statement...
CountHeadcountPriorYear =VAR targetyear = Max(LnkAssociatesPeriod[Period_Year])-1
RETURN CALCULATE(COUNTROWS(LnkAssociatesPeriod),FILTER(LnkAssociatesPeriod,LnkAssociatesPeriod[Period_Year]=targetyear),LnkAssociatesPeriod[AssociatesATSkey])
Proud to be a Super User!
Thanks so much for the help.
I used your expression but now I am getting an error that says "the following syntax error occurred during parsing; invalid token , line 2, offset 57".
Z
@Anonymous,
I create two new measures using the following DAX , both work well. Could you please post a screenshot about your scenario and share sample data of your table here?
CountHeadcountPriorYear = calculate(COUNTROWS(LnkAssociatesPeriod),FILTER(ALL(LnkAssociatesPeriod),LnkAssociatesPeriod[Period_Year]=Max(LnkAssociatesPeriod[Period_Year])-1),LnkAssociatesPeriod[AssociatesATSkey])
CountHeadcountPriorYear =VAR targetyear = Max(LnkAssociatesPeriod[Period_Year])-1 RETURN CALCULATE(COUNTROWS(LnkAssociatesPeriod),FILTER(ALL(LnkAssociatesPeriod),LnkAssociatesPeriod[Period_Year]=targetyear),LnkAssociatesPeriod[AssociatesATSkey])
Regards,
Lydia
Thanks - it worked!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |