cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hgalfre
Helper I
Helper I

Relationship with a dimension table that has several keys

Hi there! 

 

I have a few tables on my model. I managed to connect properly most of the tables (that are the classic types, like fact tables with orders or invoices and dimension tables with products or clients).

 

However, I have some "library" type tables that reference codes and their descriptions throughout the database. 

So each library table has a column with parameters (that are references to other tables' columns). Those parameters are linked to several different codes, and each code has its own description. 

 

So for instance, if I look at the clients' table:

CN (client name, unique)CC (client code, unique)GC (group code, not unique)
Starbucks001120
Fiat002121
Chrysler003121

> In this case, I have a group code that can be used for several clients that belong to the same group. 

 

 

 

Then, I have a "library" table that would reference a lot of different codes that are not unique, but are unique depending on the parameter:

ParameterCodeDescription
GC120Starbucks
GC121Fiat-Chrysler
SN121Stock120

>so in that case, the column "code" references several different codes, that are not unique on their own. However, they are unique when associated with the column parameter. 

In this example: 121 is not unique, but "WHERE Parameter=SN AND CODE=121" is unique. 

 

What I want is to use the descriptions from the libraries, rather than the codes in my visuals, as my colleagues wouldn't understand the codes. 

 

If I connect the column GC from the client table, to the column code in the library, I obviously get a problem, as the codes are not unique. 

In SQL it would be easy to solve, with a ON or WHERE condition that makes sure that I use the parameter with the code.

 

 

>What would be the most efficient and smart solution if I want to connect those two tables in power BI? 

I thought about those different options that are quite time-consuming: 

- Adding a calculated column after every code I want to get the description of > so in my example; adding a column to my client table to add "Fiat-Chrysler" for every client that has "121" in the GC column

- Duplicating my library table in several smaller tables that would only host one parameter> so a table with only the GC codes from my example.

 

>>>Or is there a functionality in PowerBI that could quickly solve this issue?

 

 

 Just so you know, I have 8 tables and 2 library tables that include a lot of parameters (of which at least 10 are interesting).

 

 

Thank you so much for your help! 😁

6 REPLIES 6
hgalfre
Helper I
Helper I

Hi edhans!

 

Thanks for the update 🙂 

 

I'll try to check your solutions. 

 

About the 1st one, just to make sure I understood right; if I unpivot the client table, then I would get up to 4 lines per client, am I right?

So I would have this if I take my previous example:

CN (client name, unique)CC (client code, unique)Key
Wholesale cars001GC_123
Wholesale cars002CT_3
Wholesale cars003GWC_002

 

Thanks!

That is correct, and may or may not be acceptable in performance. Chris Webb's BI Blog: The Pros And Cons Of Modelling Measures As A Dimension In Power BI Chris Webb'... is a good writeup on the pros and cons of this approach.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
hgalfre
Helper I
Helper I

Hi @edhans ! 

 

Thanks again for your idea.

I have implemented it, but I end up with an issue after creating the relationships between the codes column in the library and the corresponding columns in the clients table. 

 

So, I created a unique value for everycode. The library table looks like this (in green the new code I created) :

ParameterCodeDescriptionFull code
GC120StarbucksGC_120
GC121Fiat-ChryslerGC_121
SN121Stock120SN_121
CT1CoffeeCT_1
CT2CarsCT_2
WC002Wholesale groupWC_002

 

I've connected this new column to my clients list that I had updated with the new unique code (in green). But each client may have 4 different codes, that appear in different columns:

CN (client name, unique)CC (client code, unique)GC (group code, now unique)CT (company type, now unique)SN (stock name, now unique)WC (wholesaler code, now unique)
Starbucks001GC_120CT_1nullnull
Fiat002GC_121CT_2nullnull
Chrysler003GC_121CT_2nullnull
Wholesale cars004GC_123CT_3nullWC_002

 

So the Full code from the library has 1 "1-to-many" relationship to each column with a code ; GC, CT, SN and WC. 

 

However, if I test the relationship with a visual table. The table doesn't always connect the right description from the library to the client. 

 

CCName CTDescription
001StarbucksCT_1Starbucks
002FiatCT_2Cars

 

For some of the codes I insert (here, the CT code of the client), I get the right description from the table, and for some others, I get the description from another code that belongs to the client (in this example, I get Starbucks as the description, which is the GC_120 description when I would like to get the description from CT_1 (Coffee). 

 

 

 

Do you know how can I fix this?

 

I'm starting to think that I'll have to do endless duplicates of tables...

 

 

 

Thanks again for your help! 

 

You are getting into some advanced modeling here. I see 3 options I would explore. Note too that when you say you created 4 relationships all 1 to many, your model only has 1 that is active, the other 3 are inactive. You can only have one active relationship per table, but you can activate specific relationships in a measure.

  1. Unpivot your data so the keys you created are in one column. You could then use a filter to only show data in the GC or CT atribute for example. Performance and tediousness will vary greatly depending on the rest of the table layout.
  2. In a measure, you can activate the correct relationship to get the correct value. The pattern below. You have to use USERELATIONSHIP within CALCULATE. It does not create relationships. It activates inactive relationships, and temporarily deactivates the default active one.
  3. Different DIM tables per entity.
GC Measure =
CALCULATE (
    [Some Measure],
    USERELATIONSHIP ( 'DIM Table'[Unique Key], 'Fact Table'[GC] )
)

 

I cannot say which will work best. All 3 are valid, and "it depends." This is the joys of the data modeling. 🤣



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
hgalfre
Helper I
Helper I

Hi edhans, 

 

Thanks for your quick reply 😊

 

I thought about your solution yesterday night as well. Was hoping that there was a better way to connect entries from columns with full columns, but I guess no 😣 at least this option could be quicker than creating new tables. 

 

 

edhans
Super User
Super User

Power BI doesn't have joins like SQL databases. The are filter relationships, and the DIM table must be unique. You may create a compound key in Power Query using someting like =[Column1] & [Column2] & [Column3] in both your FACT and DIM table and create the filter relationship on that. You can then hide both of those fields in the model so your user never sees them and can use the description or whatever they are used to seeing.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors