Forum Discussion
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_currency_code] matches Table2[to_currency_code]
- AND Table1[currency_code] is "AED"
- AND Table1[active_status] is "A",
Then return the value of Table1[conversion_rate] else return 1
Here are the details of my tables:Table 1
from_currency_code to_currency_code active_status conversion_rateHKD HKD I 1 AUD HKD A 2.45 BHD HKD A 9.76729 CHF HKD A 4.25 EUR HKD A 4 GBP HKD A 4.8 KWD HKD A 11.98 OMR HKD A 9.539 QAR HKD A 1.00893 SAR HKD A 0.97933 USD HKD A 3.67 Table 2
currency_codeHKD AUD BHD CHF EUR GBP KWD OMR QAR SAR USD I would appreciate guidance on the best way to write the DAX formula for this calculated column since the tables are unrelated.
Thanks in advance! 😊
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 )
8 Replies
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 danextian - I need to write the DAX formula for this calculated column in Table 2, considering that the two tables are unrelated. The key point to note is that the result will not be 1 for all rows in Table 2, as each currency has a different conversion rate based on the data in Table 1.
The question now which row from table1 should table2 pick given that there can be more than one result? Calculated columns cannot return two rows.
Hi InsightSeeker ,
you can use the LOOKUPVALUE function to check the condition between the two tables, even though they are unrelated. Here’s how you can write the DAX formula:Calculated Column = IF( Table1[currency_code] = "AED" && Table1[active_status] = "A" && NOT(ISBLANK( LOOKUPVALUE( Table2[currency_code], Table2[currency_code], Table1[from_currency_code] ) )), Table1[conversion_rate], 1 )Hi Bibiano_Geraldo - I need to write the DAX formula for this calculated column in Table 2, considering that the two tables are unrelated. I have tried your suggestion but it is not giving me the desired results.
The key point to note is that the result will not be 1 for all rows in Table 2, as each currency has a different conversion rate based on the data in Table 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 LinkedInHi Kedar_Pande - I need to write the DAX formula for this calculated column in Table 2, considering that the two tables are unrelated.
