Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multi Column Relationships in the same table

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.

Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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

10 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

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

    Multi-Column-Relationships-in-the-same-table

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hi Anonymous

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.