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.
Hello
I am trying to optimice the following Dax measure:
SUMX (
FILTER(Complete_DATA,Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])),
DIVIDE (
Totales[Gross Sales],
SUMX(
FILTER(
FILTER(FX,FX[Version]=Forex[Selected Currency]),
FX[Month]=Complete_DATA[Month]),
FX[EUR to US]
)
)
)
I have 2 slicers, one to select an scenario (From the Complete_DATA table), and another to select the currency.
The idea is that the [Gross Sales] be divided by the corresponding currency (according to the scenario selected) and month independent of the "Date" as I could need 2020 sales in 2019 Exchange Rates.
FX table has the following structure:
Date | Month | Version | EUR to US |
01/01/2020 | January | LC$ | 1 |
01/01/2020 | January | USD 2020 | 1.02 |
01/01/2019 | January | USD 2019 | 1.05 |
The previous DAX formula works, but is incredibly inefficient as it first goes to each of the Complete_DATA registry, divides the corresponding currency and then summarizes everything. With small data it doesn't matter but right now I have query updates of +3 minutes.
I have tried the following with no success as Exchange is not calculating correctly:
Gross Sales 1 =
var Sales =
CALCULATE(
Totales[Gross Sales],
Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])
)
var Exchange =
CALCULATE(
[TRM],
FX[Version] in VALUES(Forex[Currency]),
FX[Month] = SELECTEDVALUE(Calendario[Month])
)
return DIVIDE(Sales,Exchange)
Any suggestions?
I am doing the following:
Gross Sales 1 =
var Sales =
CALCULATE(
Totales[Gross Sales],
Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])
)
var Exchange =
CALCULATE(
[TRM],
FX[Version] in VALUES(Forex[Currency]),
USERELATIONSHIP(Complete_Data[Month],FX[Month])
)
return DIVIDE(Sales,Exchange)
But apparently it is not calculating correclty:
The right one is the correct calculation with the old & inneficient code. Any suggestions?
Take your DAX query and analyze its query plan in DAX Studio. Check the number of produced queries, the FE/SE ratio, and the overall number of records touched. Then decide on how to change it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |