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 ,

When you need visuals to use both active and inactive relationships (and have everything respond to slicers), the trick is to use DAX measures with USERELATIONSHIP(). Static calculated tables/columns won’t update with slicer changes, so keep it all in measures. For your setup, define both relationships (active and inactive) in your model. Then, use separate DAX measures depending on what you want to show:

Example:

// Using the active relationship (Fund Code)
PerformanceMeasure =
CALCULATE(
    SUM(FundDD[PerformanceMetric]),
    USERELATIONSHIP(FundDD[Fund Code], DepartmentDD[Fund Code])
)

// Using the inactive relationship (Department Abbr)
FundsListMeasure =
CALCULATE(
    CONCATENATEX(FundDD, FundDD[Fund Name], ", "),
    USERELATIONSHIP(FundDD[Department Abbr], DepartmentDD[Department Abbr])
)

With this setup, your visuals will always respect the slicers and update automatically based on the selected relationship. If you ever want to allow users to toggle which relationship is active, check out Calculation Groups or Field Parameters (a bit advanced, but very powerful).

[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?