Forum Discussion
Matan
7 years agoFrequent Visitor
Optimize DAX Measure
Hi,
I experience slowness when i'm trying to create a measure of estimate data which is build from:
Actual SAP until date of update
+
Manual Forecast data (*date greater than Actual max date)
+
Budget data (*date greater than Forecast max date)
=
FY Estimate data by months
Here is my DAX measure:
Estimate :=
VAR ActualMaxDate =
CALCULATE (
MAX ( FactAccounts[Date] ),
FactAccounts[Info Provider (Key)] = 1,
ALL ( DimDate ),
ALL ( DimZTFA ),
ALL ( DimFA_Solo ),
ALL ( DimFA_Consolidated ),
ALL ( DimFA_SubConsolidated ),
ALL ( DimBooklet ),
ALL ( DimAccounts ),
ALL ( DimInfoProvider ),
ALL ( DimPnL )
)
VAR ForecastMaxDate =
CALCULATE (
MAX ( FactAccounts[Date] ),
FactAccounts[Info Provider (Key)] = 2,
ALL ( DimDate ),
ALL ( DimZTFA ),
ALL ( DimFA_Solo ),
ALL ( DimFA_Consolidated ),
ALL ( DimFA_SubConsolidated ),
ALL ( DimBooklet ),
ALL ( DimAccounts ),
ALL ( DimInfoProvider ),
ALL ( DimPnL )
)
VAR ACT =
CALCULATE (
[Total FactAccounts],
FILTER (
FactAccounts,
FactAccounts[Info Provider (Key)] = 1
|| FactAccounts[Info Provider (Key)] = 1.1
|| FactAccounts[Info Provider (Key)] = 1.2
)
)
VAR FC =
CALCULATE (
[Total FactAccounts],
FILTER ( FactAccounts, FactAccounts[Info Provider (Key)] = 2 ),
DimDate[Date] > ActualMaxDate
)
VAR BG =
CALCULATE (
[Total FactAccounts],
FILTER ( FactAccounts, FactAccounts[Info Provider (Key)] = 3 ),
DimDate[Date] > ForecastMaxDate
)
VAR Result = ACT + FC + BG
RETURN
IF ( Result = 0, BLANK (), Result )Can anyone help me optimize the formula?
3 Replies
- AnonymousNot applicable
Hi Matan
You can try to download DAX Studio from http://daxstudio.org
You can analyse the query performance using this and then make the necessary changes.
If you can share the pbix in One Drive or Google drive and paste the link I can take a look at it .
Cheers
CheenuSing