DAX - Filtering not working

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

@Anonymous,

It seems that you'll need to add <value>.

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

[AlB]

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]

I have added a picture to help

[AlB]

@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]

This is my code for the KPI field not LastYearKPI KPI = if([Attribute Filtered], SWITCH( [rankBy] ,"QUS",FORMAT(CALCULATE(sum(MK_PAPA_WEEKLY_PRODUCT_CARE_REPORTING[QUS]),FILTER(PivotedData,PivotedData[Attribute Value])), "#,0") ,"QUS Sales", FORMAT(CALCULATE(sum(MK_PAPA_WEEKLY_PRODUCT_CARE_REPORTING[QUS Sales]),FILTER(PivotedData,PivotedData[Attribute Value])), "£#,##0;(£#,##0)") ,blank())) This works absolutely fine and when you select a slicer of the PivotedData[Attribute Value] it filters the figure by this. I just need it so this works year on year. I can't work out how to put my code in code format in a comment

[Anonymous]

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.