Forum Discussion
Modeling multiple many-to-many relationships based around a language component
Super User
Try this solution.
1. Disconnect the Language Master table from the data model.
2. Set the cross filter direction of the dim/fact table relationships to single (dim table filters fact table).
3. Create measures that use the SELECTEDVALUE of the Language Master table (the language slicer is based on the Language Master table).
4. Use these measures as filters in the visual to display the selected language of the dim column.
Data model:
Measures:
Invoice Type Selected Language =
VAR vSelectedLanguage =
SELECTEDVALUE ( 'Language Master'[LanguageKey] )
VAR vResult =
IF ( MAX ( 'Invoice Type'[LanguageKey] ) = vSelectedLanguage, 1 )
RETURN
vResult
Sales Org Selected Language =
VAR vSelectedLanguage =
SELECTEDVALUE ( 'Language Master'[LanguageKey] )
VAR vResult =
IF ( MAX ( 'Sales Org'[LanguageKey] ) = vSelectedLanguage, 1 )
RETURN
vResult
Sum Amount = SUM ( Transactions[Amount] )
Add filters to visual:
Result:
--------------------------------------------------------------
It's actually not the visuals that are giving me trouble. We have other instances where we're doing exactly what you've shown here with disconnected tables.
The challenge is with the filters pane. Instead of having the description for a particular code repeated for every language in the filters pane, the client wants to return only the translated descriptions for the selected language. Disconnected tables would work if we added filter visuals to the report canvas, but a lot of these translated fields will be used for filtering so infrequently (relative to other fields) that they don't justify having dedicated filter visuals.