Optimize DAX Measure

[Matan]

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?

[CheenuSing]

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

[Matan]

Hi @CheenuSing,

Here is the Sample File

[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