cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ASD85
Frequent Visitor

Modeling multiple many-to-many relationships based around a language component

I have a scenario where a few of my dim tables include translations for field values into multiple languages. In each table, the native key is the combination of a language key and a code value. For example, here is what the data might look like for a table that stores invoice types:

 

ASD85_0-1624071860293.png

 

In this example, I would also have my InvoiceType field in my transactions table, and that is the field that would be used for my join. In the fact table, there is NOT a language component. My thought process was that although this would cause a many-to-many relationship between my invoice type table and my transactions table, it would effectively be treated as a one-to-many relationship in the end because there would be a mandatory filter on a single language key. 

 

My issue comes into play when I have multiple dim tables that have a language component. For example, say that I add a table for the sales organization to my example above. The relationships would look like this:

 

ASD85_1-1624072279371.png

 

I can't use the above relationships in Power BI, though, because it leads to ambiguity between the language table and the transactions table. This is a no-brainer in SQL as the desired result could be achieved with a query such as:

 

ASD85_2-1624072446377.png

 

Unfortunately, I can't wrap my head around how to achieve this in Power BI. I thought this would be a candidate for a bridge table, but I ran into the same issue there since the cartesian product of invoice type code and sales org code still resulted in ambiguity due to multiple many-to-many relationships. I think this could be accomplished by doing a cross join between the transactions table and the language master table which would then allow me to join on a concatenation of the language keys and code values so that I end up with a one-to-many to the transactions table. The downside to that is that my transactions table would get incredibly bloated due to my total records being multiplied by however many languages I have.  

 

Is there a way I can achieve the desired result of selecting a language in the language master table and having it restrict any tables with a language component to the values for the selected language and then having those values filter my fact table? 

 

 

 

8 REPLIES 8
DataInsights
Super User II
Super User II

@ASD85,

 

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:

 

DataInsights_0-1624232727942.png

 

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:

 

DataInsights_5-1624233577075.png

 

Result:

 

DataInsights_2-1624233039550.png

--------------------------------------------------------------

DataInsights_4-1624233102920.png

 

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. 

daxer
Solution Sage
Solution Sage

Here's another one but from the comments below the issue that Ruth talks about has not yet been rectified (and nobody knowns if it will ever be).

 

Multilanguage Multilingual reports in Power BI - YouTube

ASD85
Frequent Visitor

I think I've actually come across this video already. The visual headers aren't really a concern because we're already doing metadata translations through the XMLA endpoint. As far as changing the values with parameters, I don't think that's even an option for end users in an app, is it? It would also necessitate a dataset refresh I believe, and that would be a dealbreaker. 

 

I think the closest I could get to something like this would be passing through parameters in the report URL, but I'm not sure whether those persist once the user starts navigating to other pages. 

daxer
Solution Sage
Solution Sage

Following up on my previous answer... If you have translations for your dimensions, than you have to place them directly in the dimensions AS COLUMNS, not separate rows. This way all your would-be "many-to-many" will be eliminated. Then, you can create views of the cube/model in which you'll hide the columns you don't need. People then should connect to the view they want based on their language.

ASD85
Frequent Visitor

Creating copies/views of the model on a per-language basis is in my back pocket in the event that there's absolutely no other solution to this issue. That was actually the very first idea that I pitched to my client, but they are adamant about having a single report from a standpoint of simplifying both development and maintenance.

 

We are migrating their current reporting from Qlik, and Qlik handles this issue seamlessly via the ability to add conditional statements to practically any object in a report, so it would be a difficult conversation to tell them that this functionality they've utilized for years in their old solution is not available in the new solution that they're investing a lot of time and money into. 

daxer
Solution Sage
Solution Sage

Power BI has been built with the thought that brute force should be its main strength. Also, the compression algorithms make sure that milions and millions of rows are handled gracefully with speed. Secondly, many-to-many relationships in Power BI have been designed to solve one particular problem: that of granularity. You don't seem to have it in your model, so your relationships are simply wrong. Granularity issue raises when one table needs to join to another on a higher level than its own grain. A classic many-to-many relationship can only be handled, in Power BI as well as in MD cubes and data warehouses, through bridge tables. ONLY. Also, if you start creating relationships directly between fact tables, then be prepared that you'll get into big trouble sooner than might think. If you want to stay sane and have an easy time creating simple and fast DAX, please build a model that is a proper star-schema. If you forgo this advice, all hell will unleash.

 

You've been warned 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors