Forum Discussion

Matan's avatar
Matan
Frequent Visitor
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not 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

     

     

      • Anonymous's avatar
        Anonymous
        Not applicable

        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