Forum Discussion

YukiK's avatar
YukiK
Impactful Individual
3 years ago
Solved

How to Sum Semi Additive Values

Hi,

 

I have a working solution on this, but when there some conversion of some sort involved, its performance suffers a lot. Thus, I'm looking for an alterative that works better.

 

ABValueMeasure I want with better performance
12345ABC-1111010
12345ABC-1121010
12345ABC-1131010
54321EFG-1009090
54321EFG-1019090
9999ZZ-2005050
9999ZZ-2105050
9999ZZ-2205050
Total  150

 

 

I have a fact table that has dimensional column "B" which is the most granular level in the table. I have dimensional column "A" where multiple column "B" values can exist in each column "A" value. The hard part is that numeric column "Value" is at column "A", so each value is duplicated.

 

And "Value" should be summable by other dimensions, thus I'm using SUMX.

 

My current measure definition is:

 

 

 

My Measure =
SUMX (
    ADDCOLUMNS (
        VALUES ( 'Table'[A] ),
        "My Value",
            CALCULATE (
                MIN ( 'Table'[Value] ),
                ALLEXCEPT ( 'Table', 'Table'[A] )
            )
    ),
    [My Value]
)

 

 

 

 

Could you think of an alternative that performs better?

Need Help
  • YukiK's avatar
    YukiK
    3 years ago

    I was able to come up with a better solution where I just added CALCULATETABLE() around VALUES() function. Not fully understand why that performs better when used with other visual filters (with and without, both generate the exact same query plan).

    It probably has to with filter context where with CALCULATETABLE(), it'll filter values at the correct phase

     

    My Measure that Performs Better =
SUMX (
    ADDCOLUMNS (
        CALCULATETABLE( VALUES ( 'Table'[A] ) ),
        "My Value",
            CALCULATE (
                MIN ( 'Table'[Value] ),
                ALLEXCEPT ( 'Table', 'Table'[A] )
            )
    ),
    [My Value]
)

3 Replies

    • YukiK's avatar
      YukiK
      Impactful Individual
      3 years ago

      Added another column. Thanks!

  • YukiK's avatar
    YukiK
    Impactful Individual
    3 years ago

    I was able to come up with a better solution where I just added CALCULATETABLE() around VALUES() function. Not fully understand why that performs better when used with other visual filters (with and without, both generate the exact same query plan).

    It probably has to with filter context where with CALCULATETABLE(), it'll filter values at the correct phase

     

    My Measure that Performs Better =
SUMX (
    ADDCOLUMNS (
        CALCULATETABLE( VALUES ( 'Table'[A] ) ),
        "My Value",
            CALCULATE (
                MIN ( 'Table'[Value] ),
                ALLEXCEPT ( 'Table', 'Table'[A] )
            )
    ),
    [My Value]
)