Measure that ignores filter context on the page

[Matej]

Hi all, 

I have a table 'Items' and 'Inventory Levels'. In inventory levels I have two locations - A and B. On my report I have a button that will go to the detail page which will be filtered for a specific location. Now, let's say that this detail page is filtered for location A and I can see normally inventory levels for any selected item for that locaiton. What I'm trying to achieve is a measure that will ignore that page filter but still respect the row context on the selected item. In this measure I want to see current inventory levels for item ABC123 for location A and for location B even if the filter context is filtering whole page only for location A. 

I have tried this:

_Mesure =

CALCULATE(

    'Inventory Levels'[Location Available],

    FILTER(ALL(

        'Inventory Levels'),

        'Inventory Levels'[Location Name] = "Location A"

))

The issue with this measure is that it does not retain row context for a selected item - it returns same number for all items. 

I feel like I'm overlooking something really small and silly but can't figure it out. 

[PaulMac]

Could you not use the the 'Edit Interations' feature in the slicer setting to tell that table to not be affected a particular slicer? 

[Matej]

I need it to be a measure so I can then use it in conditional formating rules.

[v-yaningy-msft]

Hi, @Matej 

Long time waiting, but have you solved your problem yet? According to your description, I cannot simulate your data for the time being, if it does not solve the problem, you can share the pbix file without sensitive data (or simulate the data table that meets your needs), the output effect you expect, and the logic to achieve the effect (please try to describe it more clearly).

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-yaningy-msft]

Hi, @Matej 

According to the description of the problem you provided, it seems you want to create a measure in Power BI that will show you the inventory levels for both location A and B, regardless of the page filter, but still respect the item context. But you don't give any relevant data, you can simulate the data table or screenshot without sensitive information and the expected realization, you can try to use ALLEXPECT() as in the following MEASURE.

Inventory_Both_Locations =

CALCULATE(

    SUM('Inventory Levels'[Location Available]),

    ALLEXCEPT('Items', 'Items'[ItemID]),

    'Inventory Levels'[Location Name] IN {"Location A", "Location B"}

)

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Matej]

Hi, 

Thanks for the reply and tip. Unfortunatelly I can't share any data as I don't have access to the data model and table view, I can only work with DirectQuery model. I have adjusted measure as recommended: 

_test_measure =

CALCULATE(

    'Inventory Levels'[Location Available],

    ALLEXCEPT('Items', 'Items'[Item Name]),

    'Inventory Levels'[Location Name] IN {"A", "B"}

)

I have two slicers on the test page - Item and Location,

This is my table when only Item PA631203 is selected:

Same table with Item PA631203 and Location B selected:

My goal is to create measure in a way that would still display the stock for location A even if the location B is selected in the slicer/page filter.