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