Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Lookupvalue two tables with multiple criteria

I have below two tables. 

I try to calculate the column "Column to Calculate" using 'Tabel1' [Cost Group] to lookup in 'Tabel2' [Cost Group] and 'Tabel2' [Account] with only ABC1 and ABC2. 

The IN does not really work so looking for other ways.

Tabel1:

Cost Group Column to Calculate
ID 1
Lookupcalue('Tabel2'[Costs], 'Tabel2'[Cost Group], 'Tabel1'[Cost Group], 'Tabel2', IN {"ABC1", "ABC2"}) =3000
ID 2 
ID 3 
ID 4 
ID 5 

 

Tabel 2:

Cost GroupAccountCosts
ID 1ABC11000
ID 1ABC2

2000

ID 1ABC33000
ID 2ABC1500
ID 2ABC21000
ID 2ABC32000

 

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello Anonymous,

     

    Can you please try this DAX:

    Column to Calculate = 
CALCULATE(
    SUM('Table2'[Costs]),
    FILTER(
        'Table2',
        'Table2'[Cost Group] = 'Table1'[Cost Group] &&
        ('Table2'[Account] = "ABC1" || 'Table2'[Account] = "ABC2")
    )
)

    Should you require any further assistance, please do not hesitate to reach out to me.

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello Anonymous,

     

    Can you please try this DAX:

    Column to Calculate = 
CALCULATE(
    SUM('Table2'[Costs]),
    FILTER(
        'Table2',
        'Table2'[Cost Group] = 'Table1'[Cost Group] &&
        ('Table2'[Account] = "ABC1" || 'Table2'[Account] = "ABC2")
    )
)

    Should you require any further assistance, please do not hesitate to reach out to me.