Make measure ignore specific filter

[Anonymous]

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

[charleshale]

Can you try calculate ( [measure], allexcept ( .......the one item you're trying to have work as a filter....)?

And if you need to, you could go to the table that your filtering against and add custom columns to allow easier filters

[Anonymous]

I have the same problem - trying to calculate the Fiscal Budget for a year and need to ignore the month filter ( YYYY,MM are the filters). Why can't Microsoft make is easy like Tableau for standard time functions ?

[Anonymous]

Thanks, Greg the solution you proposed works. Really counterintuitive if I have to define the measure on the Date dimension table.

For now it works and that's all I care about

[Anonymous]

The same thing.

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

BR,

Anastasiia Lagunova

[Greg_Deckler]

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

[Anonymous]

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]

 could you solve it

[PaulDBrown]

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.

[Anonymous]

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?

[empires]

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]

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]

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]

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]

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