Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Make measure ignore specific filter

This should be simple but I just can’t solve it :) I have a report page with several filters and a measure Amounts = SUM(Table.Amount). Now I’d like Amounts to ignore just one of the filters, a Page...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.

    Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

I believe what you want is the Column variant of the ALL function. 

 

https://msdn.microsoft.com/en-us/library/ee634795.aspx

 

ALL (Column[, Column[, …]])Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table.

The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.

 

Therefore,:

 

Measure = CALCULATE([Amounts],ALL(Table[ShippingDate]))

That *should* ignore any filters on Table[ShippingDate] and preserve all other filters.

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi again

I thought so too, but in this specific case it doesn't seem to work. I narrowed it down to understand that it has to do with another filter being involved somehow!? I've created a file that illustrates this very easily with bookmarks. (Righ click and "Save link as...")

 

Download Power BI-file

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

    Please click Format on Home page->Edit interactions->select the None(highlighted in bule line), let the ShippingDate filter does not affect the visual. It will return correct result as follows.



    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      Unfortunately that’s not a solution for me. I need to solve this in DAX.