Forum Discussion
Solved
Creating a Calculated Column Between Two Unrelated Tables
Hi everyone, I have two unrelated tables in my Power BI model, and I need help creating a calculated column in Table 1. The logic for the calculated column is as follows: If Table1[from_curr...
Try this:
Test = VAR _match = NOT ( ISBLANK ( LOOKUPVALUE ( Table2[currency_code], Table2[currency_code], Table1[from currency] ) ) ) RETURN IF ( _match && Table1[currency] = "AED" && Table1[active status] = "A", Table1[conversion rate], 1 )Please take note though that as per your sample data, all rows return 1.
Hi InsightSeeker ,
Thank you for clarify me, i just updated the DAX:
Calculated Column = IF( "AED" IN DISTINCT( SELECTCOLUMNS( FILTER( Table1, Table1[from_currency_code] = Table2[currency_code] && Table1[active_status] = "A" ), "Currency", Table1[currency_code] ) ), MAXX( FILTER( Table1, Table1[from_currency_code] = Table2[currency_code] && Table1[active_status] = "A" ), Table1[conversion_rate] ), 1 )
Calculated Column =
IF(
Table1[active_status] = "A"
&& Table1[to_currency_code] = "HKD"
&& LOOKUPVALUE(Table2[currency_code], Table2[currency_code], Table1[from_currency_code], "") = "AED",
Table1[conversion_rate],
1
)
This formula works without needing to create a relationship between the tables and evaluates the logic for each row in Table 1.
💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Hi Kedar_Pande - I need to write the DAX formula for this calculated column in Table 2, considering that the two tables are unrelated.