Filter Related Table using inactive relationship

[louisGrivel]

Hello,

I am facing an issue whereby for a given dashboard, when filtering for a given value in a slicer, I need certain visuals to operate using an active 1-1 relationship whereas other visuals will need to operate based on an inactive 1-many relationship.

For context, I have 2 tables:
Table 1: DepartmentDD

Department Name	Department Abbr	Fund Code
Human Ressources	HR	AA001
Finance	FN	AA002

Table 2: FundDD

Fund Name	Fund Code	Department Abbr
Fund 1	FF001	FN
Fund 2	FF002	FN
Finance	AA002	FN

The active 1-1 relationship operates on the "Fund Code" columns, and the inactive 1-many relationship operates on the "Department Abbr" column. 

In my report, I am trying to use the 1-1 relationship to display certain performance figures relating to a selected department which sit in the FundDD table. But I am also trying to show a list of funds which are associated to a selcted department and display other pieces of information which lie in other tables which have a 1-1 relationship with the FundDD table. I want my slicer to display the list of "Department Name" of the DepartmentDD table.

I have tried to use CALCULATETABLE using USERELATIONSHIP, but the issue I am facing is that when adding columns of this calculated table, it does not update with the slicer.

Would anyone know of a way for me to achieve my objective? 

[louisGrivel · Accepted solution]

After some playing around, the solution that I found was to add a calculated column in the FundDD table which serves as a flag to denote if that row is a department or not. I did this by doing a lookup on the Fund Code. This yielded the following table:

Fund Name	Fund Code	Department Abbr	isDepartment
Fund 1	FF001	FN	0
Fund 2	FF002	FN	0
Finance	AA002	FN	1

By doing so, I was able to add the "isDepartment" column as a filter on visuals and by doing so, I was able to use the 1-many relationship and forgoe the 1-1 entirely.

[rohit1991]

Hi @louisGrivel ,

To filter a related table using an inactive relationship while ensuring different visuals operate with different relationships in Power BI, you need to use USERELATIONSHIP() inside a DAX measure, rather than using CALCULATETABLE, which creates a static table that does not dynamically respond to slicers. Since your slicer is based on Department Name from the DepartmentDD table, you need two separate measures—one that applies the active 1-1 relationship (Fund Code) for performance metrics and another that uses the inactive 1-many relationship (Department Abbr) to display related funds.

By using CALCULATE() with USERELATIONSHIP(), Power BI will dynamically switch between the two relationships based on the visual’s context. Below are the necessary DAX measures to achieve this behavior.

Using Active relationships:

PerformanceMeasure = 
CALCULATE(
    SUM(FundDD[PerformanceMetric]),  // Replace with actual metric column
    USERELATIONSHIP(FundDD[Fund Code], DepartmentDD[Fund Code])
)

Using inactive relationships:

FundsListMeasure = 
CALCULATE(
    CONCATENATEX(FundDD, FundDD[Fund Name], ", "),
    USERELATIONSHIP(FundDD[Department Abbr], DepartmentDD[Department Abbr])
)

Using these dynamic measures, the visuals will update correctly based on the slicer selection, allowing Power BI to apply the appropriate relationship dynamically per visual.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.
Connect with me on LinkedIn: Rohit Kumar

[louisGrivel]

for the FundListMeasure, that would simply return a concatenated value instead of a list that can be displayed in a table right?

[amitchandak]

@louisGrivel , calculated columns and Tables can not use slicer values. You need to create measures that can use the slicer values.

Example measure

calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date]))

[louisGrivel]

Is there an alternative method you could recommend to achieve a similar outcome?