Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to 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]
)
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]
)
Hi,
In a simple Table, please show the exact result you are expecting.
Added another column. Thanks!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
99 | |
69 | |
46 | |
39 | |
33 |
User | Count |
---|---|
163 | |
110 | |
61 | |
51 | |
40 |