Forum Discussion
Filter Related Table using inactive relationship
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?
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.
5 Replies
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]))
Is there an alternative method you could recommend to achieve a similar outcome?
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).
for the FundListMeasure, that would simply return a concatenated value instead of a list that can be displayed in a table right?
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.
