DAX optimization help required

[Anonymous]

Hi team,

 Need help on Dax optimization since Matrix Visual is not loading for high volume of SalesData while dragging this measure as Values

DAX Measure --

_mSecureReportingBaseQuantity =
VAR currentUser =
    USERPRINCIPALNAME ()
VAR isManager =
    CONTAINS (
        ALL ( DimSalesManagementTerritoryRep ),
        DimSalesManagementTerritoryRep[Manager Email], currentUser
    )
VAR isSalesRep =
    CONTAINS (
        ALL ( DimSalesManagementTerritoryRep ),
        DimSalesManagementTerritoryRep[Email], currentUser
    )
VAR selectedTerritory =
    SELECTEDVALUE ( DimTerritory[Territory with Jumper] )
RETURN
    IF (
        ISBLANK ( selectedTerritory ),
        FctSalesOrder[_mReportingBaseQuantity],
        IF (
            isSalesRep
                && CONTAINS (
                    ALL ( DimSalesManagementTerritoryRep ),
                    DimSalesManagementTerritoryRep[Management Territory], selectedTerritory,
                    DimSalesManagementTerritoryRep[Email], currentUser
                ),
            FctSalesOrder[_mReportingBaseQuantity],
            IF (
                isManager
                    && CONTAINS (
                        ALL ( DimSalesManagementTerritoryRep ),
                        DimSalesManagementTerritoryRep[Management Territory], selectedTerritory,
                        DimSalesManagementTerritoryRep[Manager Email], currentUser
                    ),
                FctSalesOrder[_mReportingBaseQuantity],
                IF ( NOT ( isSalesRep || isManager ), FctSalesOrder[_mReportingBaseQuantity] )
            )
        )
    )

Need your help at the earliest.

Thanks in advance..

Regards,

Samrat

[v-frfei-msft · Accepted solution]

Hi @Anonymous ，

To use switch instead of If should help you. Also please refer to this blog.

https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/