Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |