More efficient DAX expression

user_8834626 0
Viewed 34

Good morning Everyone, especially DAX enthusiasts I'm trying to make the following expression more efficient, but struggling to come up with an alternative:

CALCULATE ([QUANTITY (A)],  FILTER ( VALUES ( DimProduct[ProductName] ) , [CATEGORY (A-B)] = "ACTIVE" ))
        / 
           (CALCULATE (
                        [QUANTITY (A)],
                        FILTER ( ALL ( DimSite ) , [CATEGORY (A-B)] = "ACTIVE" ) ,
                        FILTER ( ALL ( DimCustomer ) , [CATEGORY (A-B)] = "ACTIVE" ) ,
                        FILTER ( ALL ( DimProduct ) , [CATEGORY (A-B)] = "ACTIVE" )
                )
        )

The denominator should look at the entire dataset for products that are "active". Anything obvious that could be done?

dax powerbi
1 Answers

Test this one. I hope It is much faster.

Performance_Measure =
DIVIDE (
    CALCULATE (
        [QUANTITY (A)],
        FILTER ( VALUES ( DimProduct[ProductName] ), [CATEGORY (A-B)] = "ACTIVE" )
    ),
    (
        CALCULATE (
            [QUANTITY (A)],
            CALCULATETABLE (
                SUMMARIZE (
                    YourFactTable,
                    DimSite[CATEGORY (A-B)],
                    DimCustomer[CATEGORY (A-B)],
                    DimProduct[CATEGORY (A-B)]
                ),
                DimSite[CATEGORY (A-B)] = "ACTIVE",
                DimCustomer[CATEGORY (A-B)] = "ACTIVE",
                DimProduct[CATEGORY (A-B)] = "ACTIVE"
            )
        )
    )
)
user_194690880
Related