Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
1 year ago
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...
Need Help
Show and Tell
  • danextian's avatar
    danextian
    1 year ago

    Hi InsightSeeker 

     

    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.

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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
)
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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
)
InsightSeeker's avatar
InsightSeeker
Helper III
1 year ago

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.

 

 

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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
)