Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
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.
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |