Creating a Calculated Column Between Two Unrelated Tables

[InsightSeeker]

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_rate

  HKD	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_code

  HKD

  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! 😊

[danextian · Accepted solution]

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.

[Kedar_Pande]

@InsightSeeker 

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

[InsightSeeker]

Hi @Kedar_Pande  - I need to write the DAX formula for this calculated column in Table 2, considering that the two tables are unrelated. 

[Bibiano_Geraldo]

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]

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 · Accepted solution]

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
)

[danextian · Accepted solution]

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.

[InsightSeeker]

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.

[danextian]

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.