Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 😁
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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!
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.
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. 🤣
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.