Multi Column Relationships in the same table

[Anonymous]

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.

[v-jiascu-msft]

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

[Anonymous]

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 🙂

[LivioLanzo]

Hi @Anonymous

with Power Query you can move the keys all to the same column and then build the relationship

[Anonymous]

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.

[LivioLanzo]

can you post your sample table ?

[Anonymous]

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

[LivioLanzo · Accepted 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

[Anonymous]

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. 

[LivioLanzo]

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?

[Anonymous]

It could be! I will try to implement what you have provided. I had to simplify and obfuscate a few things to ensure security.