PowerBI Desktop: Disable a relationship for a visual in report without deleting it

[rask]

I’m working on a Power BI report where I need to show product information in a table visual, filtered by a slicer for user profession (e.g., "Doctor"). Here’s the scenario:

Current Behavior:

• When a profession (e.g., "Doctor") is selected in the slicer (Dim_Profession), the product table (Dim_Product) only shows products linked to users who ARE doctors (via Fact_UserProfession -> Dim_User -> Dim_Product).

• Problem: I also want to show products bought by non-doctors if those products are related to the doctor profession (e.g., medical equipment bought by non-doctors).

 

I tried with CROSSFILTER, REMOVEFILTER but nothing worked. 

What I Need:

• Keep the relationship active for other reports but disable its filtering effect for this one visual.

• The product table should show:

  • Products linked to the selected profession (e.g., "Doctor") regardless of who bought them.

Question:

How can I achieve this without deleting the relationship? Is there a way to override the filter context for just this visual?

[v-dineshya]

Hi @rask ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Created sample data based on your inputs.

2. Changed the Data model. Please refer snap.

3. Created Measure "IsProductRelatedToSelectedProfession" with below DAX code.

IsProductRelatedToSelectedProfession =

VAR SelectedProfessionID = SELECTEDVALUE(Dim_Profession[Profession ID])

RETURN

IF (

    NOT ISBLANK(SelectedProfessionID)

        && SELECTEDVALUE(Dim_Product[Profession ID]) = SelectedProfessionID,

    1,

    0

)

4. You are expecting "In one table visual, you want to show products associated with the selected Profession (e.g., "Doctor") from Dim_Profession  but not restricted by who bought them".

Dragged "IsProductRelatedToSelectedProfession " in visual level filter and make it as "IsProductRelatedToSelectedProfession = 1" value.

5. In slicer, drag the values as "Dim_Profession[Profession Name] " and select the value as "Doctor".

6.  Please refer output snap and attached PBIX file for your reference.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[rask]

@v-dineshya I cannot change anything in data model as it will affect my other reports.

[v-dineshya · Accepted solution]

Hi @rask ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Thank you

[v-dineshya]

Hi @rask ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Thank you

[v-dineshya]

Hi @rask ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Thank you

[v-dineshya]

Hi @rask ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[DataNinja777]

Hi @rask ,

This is a common scenario in Power BI where you need to override the default filter context for a single visual without disrupting the data model's existing relationships. You can achieve this by creating a specific DAX measure that will act as a custom filter. This measure will essentially tell Power BI to ignore the filter path coming from the Dim User table and instead apply a direct filter from your Dim Profession slicer to the Dim Product table.

First, you will need to create a new DAX measure. You can do this by navigating to the Home or Modeling tab and selecting "New Measure". In the formula bar, you should enter the following DAX expression. This code creates the logic that will correctly identify the products to be displayed.

Filter by Product Profession =
CALCULATE(
    COUNTROWS('Dim Product'),
    REMOVEFILTERS('Dim User'),
    TREATAS(
        VALUES('Dim Profession'[Profession ID]),
        'Dim Product'[user Profession]
    )
)

After creating the measure, you need to apply it to your table visual. Select the product table on your report canvas to make it active. Then, locate the Filters pane. Drag your new Filter by Product Profession measure from the Fields list into the "Filters on this visual" section. Once it's added, a filter card will appear for the measure. Here, you will set the "Show items when the value" option to "is not blank" and apply the filter. Your visual will now display products based on their relationship to the selected profession, regardless of the user associated with them.

This method works by using several key DAX functions in combination. The CALCULATE function changes the context in which data is evaluated. Inside it, REMOVEFILTERS('Dim User') is used to specifically ignore any filters that are being propagated from the user dimension, which is the source of the unwanted behavior. Simultaneously, TREATAS creates a new, virtual relationship for this calculation only. It takes the selected Profession ID(s) from your slicer and applies them as a direct filter to the user Profession column in the Dim Product table. By setting the visual filter to "is not blank," you are ensuring that only the product rows that satisfy this new, overridden filter context are displayed in your table.

Best regards,

[rask]

@DataNinja777 This solution didn't help. The products are still filtered out based on profession even after using this measure.