Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
First post, I have looked for a previous answer on this and havent found something that fits.
I cant have multiple active relationships on the same table to multiple columns. I am trying to avoid a bridge table if possible.
So i am hoping that you Datanauts might be able to help.
The challenge.
I have two tables. Table A contains a column that has a number of unique keys. Table B has a number of columns, the key value could appear in any one of three or not at all.
I was thinking that a custom column with an IF style logic of
ID = IF TableA[KEY] matches TableB[Col1] then =TableB[Col1] else
IF TableA[KEY] matches TableB[Col2] then =TableB[Col2] else
IF TableA[KEY] matches TableB[Col3] then =TableB[Col3] else
="No Member"
Hopefully that makes sense from a logic perspective i am stuggling with the syntax.
Solved! Go to Solution.
try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3LCQAxCER78ZyL31qSlfTfxo6ysCGDhyc+nExiGqTC5gGo2SNJAE8HMDsAFi2tBMW6Oqfw/SnBTuH/XycHiF6NcRWFeZ/2Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UID = _t, A = _t, B = _t, C = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"UID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}}), AddIDCol = Table.AddColumn( ChangedType, "ID", each if [C] <> "" then [C] else if [B] <> "" then [B] else [A], type text ) in AddIDCol
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous,
Usually, the relationship is just a bridge. For example, we need to sum up the monthly sales amount through the relationship between the Date table and the FactSales table. What are you going to do? There could be two solutions in your scenario. Please download the demo in the attachment.
1. Create 1 active relationship and 2 inactive relationships. The measure could be like below.
Measure = SUM ( Query1[Sales] ) + CALCULATE ( SUM ( Query1[Sales] ), USERELATIONSHIP ( Query1[B], Table1[Key] ) ) + CALCULATE ( SUM ( Query1[Sales] ), USERELATIONSHIP ( Query1[C], Table1[Key] ) )
2. Unpivot the three columns and create a measure.
Measure 2 = sum(Query2[Sales])
Best Regards,
Dale
Thanks Dale,
you are correct this is driving a slicer on sales data. The previous soultion works in my abstraction but i have put it in the real pbix yet as its quite large and i have some presentations to do with it first. I will download your file and have a look. Thanks for your input 🙂
Hi @Anonymous
with Power Query you can move the keys all to the same column and then build the relationship
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks for your reply.
As in combine them into delimited single column and then search in there? Could you give me an example I am strugling with both the syntax and how to search for a match using a column as a source.
can you post your sample table ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Table A | TableB | ||||||
UID | Key | UID | A | B | C | ID | |
1 | 123456 | 1 | 321456 | 321456 | |||
2 | 654321 | 2 | ZZZZZZ | XXXXXX | 123456 | 123456 | |
3 | 123645 | 3 | YYYYYY | XXXXXX | 321456 | 321456 | |
4 | 321456 | 4 | YYYYYY | 321456 | 321456 | ||
5 | 231456 | 5 | 231456 | 231456 | |||
6 | 456321 | 6 | XXXXXX | 123645 | 123645 |
try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3LCQAxCER78ZyL31qSlfTfxo6ysCGDhyc+nExiGqTC5gGo2SNJAE8HMDsAFi2tBMW6Oqfw/SnBTuH/XycHiF6NcRWFeZ/2Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UID = _t, A = _t, B = _t, C = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"UID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}}), AddIDCol = Table.AddColumn( ChangedType, "ID", each if [C] <> "" then [C] else if [B] <> "" then [B] else [A], type text ) in AddIDCol
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thank you for your help and reply. I am trying to understand where in your code that it checkes the value in the TableB column to one of those sought from the table A [Key] column.
Hi spider, the code consolidates into column your IDs in order to create a collumn that you can relate to the other table in your data model. is that now what you were after?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
It could be! I will try to implement what you have provided. I had to simplify and obfuscate a few things to ensure security.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |