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.
I have a dax formula that looks at the previous year's data of difference values.
I have a slicer which I can choose between Units and Sales.
LastYearKPI = if([Attribute Filtered], SWITCH( [rankBy] ,"Units", FORMAT( CALCULATE(sum(REPORTING[Units]), FILTER( ALL(REPORTING), REPORTING[Year] = SELECTEDVALUE(REPORTING[Year] ) - 1
&& MK_PAPA_WEEKLY_PRODUCT_CARE_REPORTING[Week] = SELECTEDVALUE (MK_PAPA_WEEKLY_PRODUCT_CARE_REPORTING[Week] ))), "#,0") ,"Sales", FORMAT( CALCULATE( CALCULATE(sum(REPORTING[Sales]), FILTER( ALL(REPORTING), REPORTING[Year] = SELECTEDVALUE(REPORTING[Year] ) - 1 &&REPORTING[Week] = SELECTEDVALUE (MK_PAPA_WEEKLY_PRODUCT_CARE_REPORTING[Week] ))), FILTER(PivotedData,PivotedData[Attribute Value])), "£#,##0;(£#,##0)") ,blank()))
The filter I am trying to apply on "Sales" is not working. I have tried replacing the ALL() but to no avail.
FILTER(PivotedData,PivotedData[Attribute Value])
How would I go about doing this?
As you can see LastYearKPI column is the same figure and isn't filtered. The Online, Phone, Store is the attribute filter
Solved! Go to Solution.
@Anonymous,
It seems that you'll need to add <value>.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
@Anonymous,
It seems that you'll need to add <value>.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
Hi @Anonymous
1. Can you explain what you are trying to do for the "Sales" option in the SWITCH?
2. What exactly are you trying to do with
FILTER ( PivotedData, PivotedData[Attribute Value])
3. Can you show your data model or share the pbix? Otherwise it's kinda hard to guess what might be going on
@Anonymous
If I'm not mistaken
FILTER(PivotedData,PivotedData[Attribute Value])
will eliminate only rows for which [Attribute Value] is zero or blank( ). Do we agree?
Is that what you want to do?
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |