Forum Discussion
Filter Related Table using inactive relationship
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.
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?