Slow Measure

[Anonymous]

Hi dear, I have the following measure that currently works well however being part of a multilevel report when deploying a category that can contain up to 800 items the calculation becomes slow and consequently the matrix display my question is how can I improve that measure so that it calculates only on the current level that I am selecting (displaying) in the matrix.

Volume3:=SWITCH(TRUE(),
HASONEVALUE(Sales[Item]),[Volume2],
HASONEVALUE(Division[DivShort]),(DIVIDE(Sales[Qtty Current],Sales[Qtty Last],0)-1)*Sales[MXP-USD Last],
HASONEVALUE(Customer[SECTOR]),(DIVIDE(Sales[Qtty Current],Sales[Qtty Last],0)-1)*Sales[MXP-USD Last],
HASONEVALUE(Customer[Holding]),(DIVIDE(Sales[Qtty Current],Sales[Qtty Last],0)-1)*Sales[MXP-USD Last],
HASONEVALUE(Vendor[Name]),(DIVIDE(Sales[Qtty Current],Sales[Qtty Last],0)-1)*Sales[MXP-USD Last],
HASONEVALUE(Sales[CURR]),SUMX(VALUES(Class[Description Class]),Sales[ZVolume2]),
BLANK()
)

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

I would suggest you could create several measures to achieve that.For example:

Measure1=(DIVIDE(Sales[Qtty Current],Sales[Qtty Last],0)-1)*Sales[MXP-USD Last]

Measure2=SUMX(VALUES(Class[Description Class]),Sales[ZVolume2])

Volume3 :=
SWITCH (
    TRUE (),
    HASONEVALUE ( Sales[Item] ), [Volume2],
    HASONEVALUE ( Division[DivShort] ), [Measure1],
    HASONEVALUE ( Customer[SECTOR] ), [Measure1],
    HASONEVALUE ( Customer[Holding] ), [Measure1],
    HASONEVALUE ( Vendor[Name] ), [Measure1],
    HASONEVALUE ( Sales[CURR] ), [ Measure2],
    BLANK ()
)

Or

Volume3 :=
IF (
    HASONEVALUE ( Sales[Item] ),
    [Volume2],
    IF (
        HASONEVALUE ( Division[DivShort] ) || HASONEVALUE ( Customer[SECTOR] )
            || HASONEVALUE ( Customer[Holding] )
            || HASONEVALUE ( Vendor[Name] ),
        [Measure1],
        IF ( HASONEVALUE ( Sales[CURR] ), [ Measure2] )
    )
)

Regards,