CODEWITHSUNDEEP
powerbidax
Kevin D
Kevin D

Reputation: 98

More efficient DAX expression

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?

Upvotes: 1

Views: 47

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

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"
            )
        )
    )
)

Upvotes: 1

Related Questions