cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Multiplication with lookup()

I have a fact table as below
Curreny  Amount

usd          100

eur           75
cny           50

slicertable

Curreny  rate_1    rate_2    rate_3

usd          1            1            1

eur           0.75      0.85         0.95
cny           0.50      0.65         0.80

rate_1_measure = 100*1 + 75*0.75 + 50*0.50

what I am trying is

rate_1_measure  =
PRODUCTX(facttable[Amount],
LOOKUPVALUE(slicertable[rate_1],
slicertable[Currency],  facttable[Currency]))

How can I create this meaure correctly in dax ?
1 ACCEPTED SOLUTION
Super User

Hi @Setosa

Please try below DAX in measure

``````Rate_1_Equivalent =
VAR CurrencyAmounts = SUMMARIZE('Facttable', facttable[Curreny], 'Facttable'[Amount])
VAR USD_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "usd"), 'slicertable'[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "eur"), 'slicertable'[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "cny"), 'slicertable'[rate_1])

RETURN
SUMX(
CurrencyAmounts,
SWITCH(
facttable[Curreny],
"usd", 'Facttable'[Amount] * USD_Rate_1,
"eur", 'Facttable'[Amount] * EUR_Rate_1,
"cny", 'Facttable'[Amount] * CNY_Rate_1
)
)``````

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush

3 REPLIES 3
Super User

Hi @Setosa

Please try below DAX in measure

``````Rate_1_Equivalent =
VAR CurrencyAmounts = SUMMARIZE('Facttable', facttable[Curreny], 'Facttable'[Amount])
VAR USD_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "usd"), 'slicertable'[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "eur"), 'slicertable'[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "cny"), 'slicertable'[rate_1])

RETURN
SUMX(
CurrencyAmounts,
SWITCH(
facttable[Curreny],
"usd", 'Facttable'[Amount] * USD_Rate_1,
"eur", 'Facttable'[Amount] * EUR_Rate_1,
"cny", 'Facttable'[Amount] * CNY_Rate_1
)
)``````

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush

Frequent Visitor

thank you very much.

Super User

Hi @Setosa

If it is working, please mark my PREVIOUS comment as SOLUTION

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors