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.
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?
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
Hi @Matan,
I checked the file using DAXStudio, the measure Estimated takes only 52 ms. Please let me know where you find the slowness.
Is it when building a table / matrix table or bar chart or line chart.
Cheers
CheenuSing
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |