Isolating context more effectively need help

[Oliver_Buchanan]

Hi Power BI Community,

Link to Sample pbix File

https://drive.google.com/file/d/1ZEov7hV7zN6ZzioZ7pkFi_LTlFAZrF4c/view?usp=sharing

I’m having some trouble isolating context within my Power BI model and could really use some help.

Here’s the situation: I’m working with a matrix visual that pulls data from multiple tables, including ExportRail and DomesticRail, and I'm running into challenges with unintended context propagation between these tables when using tootips. The model initially had direct relationships between tables, including a one-to-one SiteHolidayKey relationship, but I removed this as it was creating ambiguity.

Currently, I have broken out columns into separate tables to reduce the propagation issues. Specifically, I have separate dimension tables for Site Name and Holiday, and I'm relating the fact tables (ExportRail, DomesticRail) to these dimension tables with many-to-one relationships. However, even after these adjustments, my measures still seem to be finding context where they shouldn't, leading to incorrect evaluations in my matrix visual.

Here's an example of one of the evaluation measures I'm working with:

Export Rail Evaluation = 
IF(
    HASONEVALUE('ExportRail'[Does Fonterra req export rail]),
    MAX('ExportRail'[Has Coda confirmed export rail]),
    "No Context"
)

The intended behavior is to evaluate context only when the specific columns (Does Fonterra req ... and Has Coda confirmed ...) are relevant. However, I keep getting incorrect evaluations, and it seems like the visual is finding context where it should not, despite the fact that there is no direct path between these tables in the relationship diagram.

Does anyone have experience with controlling or isolating context more effectively in a situation like this? I’m particularly interested in ensuring that context from unrelated tables does not influence the measure evaluation.

Any insights or suggestions would be greatly appreciated!

Thanks in advance!

[Oliver_Buchanan · Accepted solution]

@VahidDM actually i think i see what's maybe causing the issue. I've been running under the assumption that when hovering over a specific cell value on the matrix it will be applying a filter on the specific row you are hovering over. But when i click on the specific cell element its highlighting both rows. So i think the matrix visual itself it's allowing me to propagate the context as I am intending.

[Oliver_Buchanan]

Further to this, way to actually achieve the results i was looking for was to concatenate the Site Name and Responses into a single column, then rejig relationships around that level of granularity instead and then use a dynamic measure in the Values of my Matrix as well. The table is a little bit less visually appealing because I liked the header - subject spacing but it allows for the dynamic tool tip to work.

[VahidDM]

Hi @Oliver_Buchanan 

Unintended context propagation happens when visuals include fields from multiple tables, causing measures to receive filters from unrelated tables. To isolate context in your measure and prevent it from being influenced by other tables, modify your measure using CALCULATE and REMOVEFILTERS:

Export Rail Evaluation =
IF(
    HASONEVALUE('ExportRail'[Does Fonterra req export rail]),
    CALCULATE(
        MAX('ExportRail'[Has Coda confirmed export rail]),
        REMOVEFILTERS('DomesticRail')
    ),
    "No Context"
)

This ensures the measure only considers filters from the 'ExportRail' table. Additionally, check your matrix visual to ensure it doesn't include fields from unrelated tables that could introduce unwanted context. Keeping the visual focused on 'ExportRail' fields will help prevent these issues.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[Oliver_Buchanan]

Hi @VahidDM,

Thank you very much for your response.

This unfortunately doesn't help me as I am trying to achieve the end result where when hovering over a specific cell element in the matrix visual, the tooltip shows only relevant data.

For example when hovering over the cell in below snip. You can see i'm pulling back the related value from the [Has Coda Confirmed Export Rail column]. But I need the Domestic rail evaluation measure to pull back "no context". (as none should be found when under the context of [Does Fonterra req export rail])

Plugging in your suggested measure seems to remove all filters entirely (including ones we want) meaning that it is always outputting "Yes" even when that is not what the related value of the [Has Coda Confirmed] Column should be. At least in my original measure the output updates to the correct value for a specific [Site Name] and [Holiday] combination. 

[VahidDM]

Can you try this:
To prevent unintended context propagation and ensure your measure only considers relevant filters, you can redefine your measure by explicitly setting the filter context. Here's how:

Export Rail Evaluation =
VAR CurrentSite = SELECTEDVALUE('Site Name'[Site Name])
VAR CurrentHoliday = SELECTEDVALUE('Holiday'[Holiday])
VAR DoesFonterraReq = SELECTEDVALUE('ExportRail'[Does Fonterra req export rail])
RETURN
IF(
    NOT(ISBLANK(DoesFonterraReq)),
    CALCULATE(
        MAX('ExportRail'[Has Coda confirmed export rail]),
        FILTER(
            ALL('ExportRail'),
            'ExportRail'[Site Name] = CurrentSite &&
            'ExportRail'[Holiday] = CurrentHoliday &&
            'ExportRail'[Does Fonterra req export rail] = DoesFonterraReq
        )
    ),
    "No Context"
)

Checks if DoesFonterraReq is not blank to ensure context exists.

Uses CALCULATE with FILTER and ALL('ExportRail') to explicitly apply filters only on the 'ExportRail' table based on the current selections.

This approach isolates the measure from unintended filters from other tables like 'DomesticRail'.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[Oliver_Buchanan]

Hi @VahidDM,

Thank you have tried this approach as well. At a surface level it looks like it should work, its explicitly stating the filters we want to apply to the measure and defining the context to the level of granularity we need.

However it doesn't seem to be working still. It's now outputting the correct values for the [DOes Fonterra req export rail] rows (similar to the original measure). But it's still finding context on the [Does Fonterra req domestic rail] rows and showing a value instead of "No Context".

It's odd but my gut feel is somehow this part here, it's somehow pulling a value from the columns of the ExportRail table, when we're looking at columns in the DomesticRail table.

VAR DoesFonterraReq = SELECTEDVALUE('ExportRail'[Does Fonterra req export rail])

Is PowerBI assuming some relationship in the background between these two tables?

I've even tried editing the matrix to ONLY have the [Does Fonterra req domestic rail] column from the DomesticRail table, and this export rail evaluation measure still finds context.

[Oliver_Buchanan]

For context the relationships are set up as below