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 c...
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,

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,