Forum Discussion
DAX - Filtering not working
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
Anonymous,
It seems that you'll need to add <value>.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
6 Replies
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
- I have two columns, units and sales. I have a slicer which means I can switch between the two as the values for a table. I then have a table where I want to compare the year on year variance for either sales or units. My year and week format is 'yyyy-dd' hence not a date format. I then want to be able to filter it by PivotedData[Attribute Value] but I cannot. In the "Sales" part of the DAX I have wrapped the expression in a CALCULATE(...FILTER(PivotedData,PivotedData[Attribute Value]) but it makes no difference. It just gives the total.
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?
Anonymous,
It seems that you'll need to add <value>.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
