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

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,