Conditional Column from Multiple Tables

[Anonymous]

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

[AllisonKennedy]

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]

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]

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]

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

[Anonymous]

Thank you Allison, 

I appreciate if you mentioned that 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. It was key factor and achieved desired results.