Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Slow Measure

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()
)
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,