Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Conditional Column from Multiple Tables

Hi All, 

 

I have 4 tables : Main, Chq, Cash, ETRF and lookinto create Custom Column in Main Table.

 

Custom Column in Main Table:

 

Return "Cash" if Acc_no is in Cash Table 

 

Return "Chq" if Acc_no is in Chq Table 

 

Return "ETRF" if Acc_no is in ETRF Table 

 

Chq, Cash, ETRF tables contain Acc_no and linked to Main Table which has Acc_no too

 

What is the best way to generate results?

 

Thanks in Advance

Need Help

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    If I understand your problem correctly you should be able to try something like this, depending on how your relationships are setup:

     

    Custom Column = IF(RELATED(CashTable[Acc_no])=MainTable[Acc_no], "Cash", 

    IF(RELATED(ChqTable[Acc_no])=MainTable[Acc_no], "Chq",

    IF(RELATED(ETRFTable[Acc_no])=MainTable[Acc_no], "ETRF",

    "")))

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    A custom column in the fact table something like this maybe:

    whichDim = SWITCH (TRUE(),
    NOT ISBLANK( RELATED(TableDim1[accnum])), "Dim1", 
    NOT ISBLANK( RELATED(TableDim2[accnum])), "Dim2",
    NOT ISBLANK( RELATED(TableDim3[accnum])), "Dim3", "n/a")

    with your versions of tablenames and strings

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much. Custom Column query is recognising only one table : TableDim1

       

      Main Table:Table Dim1 Relationship *.1 

       

      Main Table:Table Dim2 Relationship 1.*

       

       Main Table:Table Dim3 Relationship 1.*

       

      Do you know why? I believe the error is because how relationship is set up. 

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        6 years ago

        It is most likely due to cross filter direction. The arrows need to point to your main table in all three relationships for this to work. Since your relationships are 1 to many and many to 1, this doesn't happen for all of them by default. You can change the cross-filter direction to both, just read up on that a bit as it may have some other impact on other data in your visualizations. 

         

         

        Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

         

        If you found this post helpful, please give Kudos.

        I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

        https://sites.google.com/site/allisonkennedycv