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