Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
YukiK
Impactful Individual
Impactful Individual

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?

1 ACCEPTED SOLUTION
YukiK
Impactful Individual
Impactful Individual

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

View solution in original post

3 REPLIES 3
YukiK
Impactful Individual
Impactful Individual

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
YukiK
Impactful Individual
Impactful Individual

Added another column. Thanks!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.