Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 filter on Table.ShippingDate. By ignore I mean that Amounts should calculate as if that filter doesn’t exist. How to do this using DAX? I’ve tried everything I can think of using CALCULATE ALL, ALLEXCEPT, FILTER etc. but nothing seem to work.
Can anybody help me?

 

--- EDIT ---

I've narrowed this problem down something I've illustrated clearly in this Power BI report. Please check it out and see if you can understand why Bookmark 1-problem occurs:

https://drive.google.com/file/d/1PqKp5wjAEwHuL2UU-gUjFb39WrVas4qn/view?usp=sharing

1 ACCEPTED SOLUTION

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!

 

 

View solution in original post

34 REPLIES 34
Tom_Y
Helper III
Helper III

For anyone who has been struggling for days/ hours, this is my solution to my problem, the "All" is working, but you need to plan if you want to put it first, or you want to it after your filter in calculation.
Mine is that I need to calculate based on specific year filter (e.g. 4 years average in 2020-2024), but then I can't let it influenced by filter in Matrix.

Household_FY_20/
24 =
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('Project_Table'[ClientID]),
        'Date_Table'[FY] IN {"FY20/21", "FY21/22", "FY22/23", "FY23/24"},
        ALL('Date_Table'[FY])
    ),
    4
)
Nelson-Wong
Frequent Visitor

I found this works perfectly.

CALCULATE(expression, ALL())

doesnot work

Anonymous
Not applicable

Hey, I had a similar situation that I found a solution to. I needed the sum of Table1.Amount, but ignoring any filters on Table1.Filter. I tried "Calculate( Sum( Table1.Amount) , All(Table1.Filter))" but this did not work. It didn't work because even though Table1.Filter was not being directly filtered, it was being crossfiltered by Table2.Filter. To ignore the crossfilter, I tried this and it worked:

 

Calculate( Sum( Table1.Amount) , All(Table1.Filter), All(Table2.Filter)  )

Thank you @Anonymous!! Solved my problem perfectly!

Anonymous
Not applicable

Hey guys!

It's dead simple, really. Use the ALL function to remove a filter on a specific column like this:

Measure = calculate(Expression,ALL(column-you-want-to-remove-filter))

That's it. ALL, used inside a CALCULATE expression, works like REMOVEFILTER.

this does not work anymore

What if the filter is also using a measure (visual level filter)?

 

Wow, thank you!

 

I really needed this simplicity for something I am working on right now. I really was searching all over the place in very much too complex challenges and solutions. It really was as simple as the DAS you wrote down. 

Anonymous
Not applicable

@Greg_Deckler 

Hi Greg,

 

My measure is giving wrong column sub total but correct row sub total.

 

My Actual Measure in the report:

 

Measure =

VAR Y=
CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name])]))

VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))

Return x/Y

Output I am getting:
 
EmployeeProject1Project2Total
A0.300.3
B0.210.190.25(NOT CORRECT)
C00.30.3
Total0.510.491


It is coming wrong as the measure is behaving at the backend like below ie ( Filtering across the project)
 
Measure = 
VAR Y=
CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name]),ALLSELECTED('Project Details'[Complete Project Detail]))  <--- Filtering

VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))

Return x/Y

Can you please suggest the solution as I am unable to avoid filtering and fix this

Yes, confirmed. It works with Calculate([expression],ALL([column name])).

Anonymous
Not applicable

I see a lot of responses that it works with ALL, but no one adresses the specifik problem/bug in my example (download file below to see my example). There I illustrate that in some cases it won't work.

Download file

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!

 

 

Thanks alot for that info, that solved my problem! Star Schemas ftw. 

I have created a different distinct values table for Month+Year, and used ALL( Table2[Month+Year]) in CALCULATE function. Still doesn't work for me! any other solution I can try out? 

Anonymous
Not applicable

Thank you! Finally someone cares to understand the problem thoroughly and not just throws out a guess!
Anonymous
Not applicable

Hi,

 

Not sure if this is what you are looking for, but if you select a slicer and then go to the format menu and click "edit interactions" you can turn off a visual you don't want to be filtered by that slicer

 

Thanks,

Anonymous
Not applicable

Yup, I figured it out.

 

I also created a measure to do the same for another dashboard.

 

Thanks for responding though. Power BI community is great !!

Anonymous
Not applicable

Would you please let me know how did you resolve this I am facing similar issue. TIA

tananich
Frequent Visitor

Hi, has anyone solved this?

I'm having smth similar in PowerBI,  I need to completely ignore one of columns in my measure, using DAX.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.