Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a dax code as follows and I need to add a filter argument but somehow the result is coming unexpected.
VAR CurrencyAmounts =
SUMMARIZE(
FILTER(facttable, facttable[COL] = "abc"),
facttable[Currency], 'facttable'[Amount])
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
)
)
Solved! Go to Solution.
Hi @Setosa ,
Please try the following methods and check if they can solve your problem:
1.Create the simple table.
2.Create the new measure to filter.
Rate_1_Equivalent =
VAR CurrencyAmounts =
SUMMARIZE(
FILTER(facttable, facttable[COL] = "abc"),
facttable[Currency],
"TotalAmount", SUM(facttable[Amount])
)
VAR USD_Rate_1 = MAXX(FILTER(slicerTable, slicerTable[Currency] = "usd"), slicerTable[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER(slicerTable, slicerTable[Currency] = "eur"), slicerTable[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER(slicerTable, slicerTable[Currency] = "cny"), slicerTable[rate_1])
RETURN
SUMX(
CurrencyAmounts,
SWITCH(
[Currency],
"usd", [TotalAmount] * USD_Rate_1,
"eur", [TotalAmount] * EUR_Rate_1,
"cny", [TotalAmount] * CNY_Rate_1
)
)
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Setosa ,
Please try the following methods and check if they can solve your problem:
1.Create the simple table.
2.Create the new measure to filter.
Rate_1_Equivalent =
VAR CurrencyAmounts =
SUMMARIZE(
FILTER(facttable, facttable[COL] = "abc"),
facttable[Currency],
"TotalAmount", SUM(facttable[Amount])
)
VAR USD_Rate_1 = MAXX(FILTER(slicerTable, slicerTable[Currency] = "usd"), slicerTable[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER(slicerTable, slicerTable[Currency] = "eur"), slicerTable[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER(slicerTable, slicerTable[Currency] = "cny"), slicerTable[rate_1])
RETURN
SUMX(
CurrencyAmounts,
SWITCH(
[Currency],
"usd", [TotalAmount] * USD_Rate_1,
"eur", [TotalAmount] * EUR_Rate_1,
"cny", [TotalAmount] * CNY_Rate_1
)
)
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.