cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Deanamite Regular Visitor
Regular Visitor

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?

 

PowerBI LY.JPG 

 

As you can see LastYearKPI column is the same figure and isn't filtered. The Online, Phone, Store is the attribute filter

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX - Filtering not working

@Deanamite,

 

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

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: DAX - Filtering not working

Hi @Deanamite

 

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

Deanamite Regular Visitor
Regular Visitor

Re: DAX - Filtering not working

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.
Deanamite Regular Visitor
Regular Visitor

Re: DAX - Filtering not working

I have added a picture to help
Super User
Super User

Re: DAX - Filtering not working

@Deanamite

 

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?

Deanamite Regular Visitor
Regular Visitor

Re: DAX - Filtering not working

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
Community Support Team
Community Support Team

Re: DAX - Filtering not working

@Deanamite,

 

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

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.