Relationship with a dimension table that has several keys

[hgalfre]

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)
Starbucks	001	120
Fiat	002	121
Chrysler	003	121

> 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:

Parameter	Code	Description
GC	120	Starbucks
GC	121	Fiat-Chrysler
SN	121	Stock120

>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! 😁

[hgalfre]

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 cars	001	GC_123
Wholesale cars	002	CT_3
Wholesale cars	003	GWC_002

Thanks!

[edhans]

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.

[hgalfre]

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) :

Parameter	Code	Description	Full code
GC	120	Starbucks	GC_120
GC	121	Fiat-Chrysler	GC_121
SN	121	Stock120	SN_121
CT	1	Coffee	CT_1
CT	2	Cars	CT_2
WC	002	Wholesale group	WC_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)
Starbucks	001	GC_120	CT_1	null	null
Fiat	002	GC_121	CT_2	null	null
Chrysler	003	GC_121	CT_2	null	null
Wholesale cars	004	GC_123	CT_3	null	WC_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. 

CC	Name	CT	Description
001	Starbucks	CT_1	Starbucks
002	Fiat	CT_2	Cars

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! 

[edhans]

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. 🤣

[hgalfre]

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]

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.