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 )
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
)