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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

31 REPLIES 31
Anonymous
Not applicable

The same thing.

It's very simple  work by fixed lod in Tableau but in PBI...

 

BR,

Anastasiia Lagunova

@Anonymous, @Anonymous, @tananich@manito969@empires - OK, I believe that this is an issue that @marcorusso and I took a look at in another thread and Marco wrote up a nice blog article about. I have the fix in the attached PBIX. Basically, take ShippingDate and create another table using DISTINCT('Table'[ShippingDate]) Then relate the tables. Use this other table as your slicer and change your measure to refer to this new table. As Marco explains, this is not technically a bug but a pre-filtering "feature" of DAX.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,
Fairly new to PBI and trying to incorporate your solution into my measure, and I can't work out where I'm going wrong, please could you take a look at the below? Essentially trying to make this measure ignore the month filter on the page and pull back the full year's budget

NET_PAIRS_FY_TOTAL_BUD/FOR_RetRocks =
CALCULATE(
    SUM,('FP&A Profit & Loss Data'[QUANTITY]),
    ALL('FP&A Profit & Loss Data'[MONTH]),
    FILTER('FP&A Profit & Loss Data',
    'FP&A Profit & Loss Data'[TIER_2] = "Group Underlying Operating"
    && 'FP&A Profit & Loss Data'[LEVEL_THREE_BRANCH_ACCOUNTS] IN {"Gross Sales", "Returns"}
    && 'FP&A Profit & Loss Data'[FOOTWEAR] IN {"Footwear"}
    && 'FP&A Profit & Loss Data'[REGION] IN {"Americas", "EMEA", "Greater China", "India", "Japan & Korea", "South East Asia", "UK & ROI", "Region Adjustment"}
    && 'FP&A Profit & Loss Data'[VERSION] = SELECTEDVALUE('version_sort'[Version])
    && 'FP&A Profit & Loss Data'[YEAR] = 2023)
)
manito969
Frequent Visitor

 could you solve it

PaulDBrown
Community Champion
Community Champion

How about something along the lines of:

 

ignore filter  ‘column x’= IF(ISFILTERED(‘table[column x]), [measure 1], [measure 2])

 

if you don’t want to compute [measure 1], you can substitute it for BLANK() for example.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Thanks, but I don’t see how ISFILTERED could solve this problem!? Can you illustrate how you’d successfully apply it in the example file I posted above?

Hey buddy, did you solve this problem? I'm facing the same issue on PowerBI, I have two different data segmentation filter (on date columns 'A' and 'B') and I need to completely ignore one of filters on measure, using DAX, because my measure is used on other formulas.

 

How could we solve this problem?

 

I think ISFILTERED function doesn't work because regardless of the column 'A' is filtered or not, I need to ignore filter only on that column 'A'.

Greg_Deckler
Super User
Super User

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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.

2.PNG

Best Regards,
Angelia

Anonymous
Not applicable

Unfortunately that’s not a solution for me. I need to solve this in DAX.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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