How to Sum Semi Additive Values

[YukiK]

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.

A	B	Value	Measure I want with better performance
12345	ABC-111	10	10
12345	ABC-112	10	10
12345	ABC-113	10	10
54321	EFG-100	90	90
54321	EFG-101	90	90
9999	ZZ-200	50	50
9999	ZZ-210	50	50
9999	ZZ-220	50	50
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?

[YukiK · Accepted solution]

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

[Ashish_Mathur]

Hi,

In a simple Table, please show the exact result you are expecting.

[YukiK]

Added another column. Thanks!