topic Re: Sum of expression to respect LOD in DAX Commands and Tips

Sum of expression to respect LOD

bamber — Sun, 12 Sep 2021 10:50:55 GMT

Hi,

I can't figure out a DAX statement that would correctly sum results of another expression.
I've built a table visual, in which [Expression] is a simple IF statement which checks if sum of [Metric] is lower or equal than a set maximum value (i.e. [Cap]) and then returns either the sum or [Cap]

TABLE VISUAL 1
Dim A	Dim B	Sum of Metric	Cap	Expression
A	A1	3	5	3
A	A2	4	5	4
A	A3	7	5	5
A	A4	100	5	5
B	B1	12	12	12
B	B2	14	12	12
B	B3	7	12	7
B	B4	25	12	12

The Expression goes like this:

Expression =
IF
(
SUM([Metric]) > MIN([Cap]),
MIN('DataTable'[Cap]),
SUM([Metric])
)

Now I would like to build another table visual that would correctly sum [Expression] by Dim A and would be possible to filter by other dimensions in the Data Tabe (Dim C, D, E... that are not used in the visual)

TABLE VISUAL 2
Dim A	Sum of Expression
A	17
B	43

I tried CALCULATE with SUMX and ALLEXCEPT on [Dim A] and [Dim B] but results make no sense.
In Tableau I would probably use something like SUM({FIXED [Dim A],[Dim B]:[Expression]}), but I'm struggling to replicate this logic in DAX.

What would be the correct DAX for [Expression] and [Sum of Expression]?

Re: Sum of expression to respect LOD

Fowmy — Sun, 12 Sep 2021 11:10:55 GMT

bamber

You expression should be :

Expression = SUMX( Table2, IF( Table2[Metric] > Table2[Cap], Table2[Cap], Table2[Metric] ) )

My sample data:

Re: Sum of expression to respect LOD

bamber — Sun, 12 Sep 2021 12:00:32 GMT

Hi Fowmy , thanks for replying so quickly.

Your solution indeed works with a much simplified dataset, however, my data is a bit more complicated.
Each [Dim B] has its [Cap] value and there are other dimensions - that's why I used SUM and MIN to evaluate [Expression] in my initial approach.
The actual dataset looks more like that (sorry, I can't share real data; I just added one more dimension to simulate granularity):

Dim A	Dim B	Dim C	Metric	Cap
A	A1	C1	3	15
A	A2	C2	4	100
A	A3	C3	7	20
A	A4	C1	100	200
B	B1	C2	12	22
B	B2	C3	14	13
B	B3	C1	7	50
B	B4	C2	25	90
A	A1	C3	3	15
A	A2	C1	4	100
A	A3	C2	7	20
A	A4	C3	100	200
B	B1	C1	12	22
B	B2	C2	14	13
B	B3	C3	7	50
B	B4	C1	25	90
A	A1	C2	3	15
A	A2	C3	4	100
A	A3	C1	7	20
A	A4	C2	100	200
B	B1	C3	12	22
B	B2	C1	14	13
B	B3	C2	7	50
B	B4	C3	25	90

With dataset like this, the approach with SUMX of an IF statement doesn't return correct results.

Re: Sum of expression to respect LOD

Fowmy — Sun, 12 Sep 2021 12:12:18 GMT

bamber

Can you share the expected result based on this data set?

Re: Sum of expression to respect LOD

bamber — Sun, 12 Sep 2021 12:20:13 GMT

Sure. That would be:

Dim A	Sum of Expression
A	241
B	131

Re: Sum of expression to respect LOD

VahidDM — Mon, 13 Sep 2021 02:16:29 GMT

Hi bamber

Try this measure:

Expression = VAR _SumTable = SUMMARIZE ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B], "Expression", IF ( CALCULATE ( SUM ( 'DataTable'[Metric] ), ALLEXCEPT ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B] ) ) < MIN ( 'DataTable'[Cap] ), CALCULATE ( SUM ( 'DataTable'[Metric] ), ALLEXCEPT ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B] ) ), MIN ( 'DataTable'[Cap] ) ) ) RETURN SUMX ( _SumTable, [Expression] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

Re: Sum of expression to respect LOD

bamber — Mon, 13 Sep 2021 13:52:05 GMT

VahidDM
Works perfectly. Thanks!