help with complex Measure

[Anonymous]

Hi, I really apreciate if any one can help me with this problem

Data looks like this table

ID School	Level	Service	Qty
101	primary school	Lunch	20
101	primary school	Breakfast	18
101	primary school	snacks	7
101	high school	Lunch	30
101	high school	Breakfast	40

Its required a measure that sum max qty by level

Max Qty primary school	20
Max Qty high school	40
Measure result expected	60

[KNP]

SumOfMax =
SUMX (
SUMMARIZECOLUMNS (
Table[Level],
"MaxOfLevel", MAX ( Table[Qty] )
),
[MaxOfLevel]
)

[TomMartens]

Hey,

it's not possible to use SUMMARIZECOLUMNS inside a table iterator.

Regards,

Tom

[KNP]

@TomMartens - I may be misunderstanding you but my testing would tend to disagree. I'm definitely no DAX expert though.

[Anonymous]

thanks, solution works but if I apply a context filter the visual show error

[KNP]

Thanks for the additional example.

I'm not sure what the issue is with the filter. I'd need more detail.

The way I would solve it would be...

SumOfMax =
SUMX (
    SUMMARIZECOLUMNS (
        'Table'[ID School],
        'Table'[Level],
        "GroupedMax", MAX ( 'Table'[Qty] )
    ),
    [GroupedMax]
)

May pay to catch up on the conversation I had with Tom above as this may not be considered to be best practice.

Otherwise, maybe wait for @TomMartens to answer, as I'm not sure how to rewrite the "VALUES()" version of the measure to include the two grouping levels you need.

[TomMartens]

Hey @KNP ,

please excuse, I've been a little strict saying

"it's not possible to use SUMMARIZECOLUMNS inside a table iterator",

I should have said

"one should not use SUMMARIZECOLUMNS inside a measure".
This is because the function is not fully supported, for this reason, I do not use it to avoid building habits, which then become a roadblock if I have to rewrite a measure or pattern that I'm trying to adapt to a certain use case where SUMMARIZECOLUMNS can not be used. Here you will find an article, that explains this in much more detail: Introducing SUMMARIZECOLUMNS - SQLBI

Once again, please excuse me for being that strict, and of course, the measure works perfectly for this requirement.

Regards,

Tom

[KNP]

Hi Tom,

Thanks for the clarification.

I understand what you're saying. It works in this case but not best practice.

DAX is definitely the weaker part of my skillset so I'm always happy to learn from others.

I feel like I should caveat my DAX related answers on here with "I'm no DAX expert", but then again, I feel like all answers in the community come with an implicit caveat. 

Regards,

Kim

[TomMartens]

Hey @Anonymous 

this measure:

sum over max = 
SUMX(
    VALUES( 'Table'[Level] )
    , CALCULATE( MAX( 'Table'[Qty] ) )
)

returns what you are looking for:

Using a table iterator function, here SUMX, is necessary because a row header that becomes part of the filter context is not present. As the MAX value has to be calculated for each school level VALUES( level ) is used to determine the table used for iteration. On the total line, There is an iteration across two rows (high school, primary school), inside the body of the table visual there is just one row, as the "row header" implicitly "filters" the table.

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Hi, thanks! works for the example but still have a little problem, when apply this measure to full table with several ID School don't show the right value,

ID School	Level	Service	Qty
101	primary school	Lunch	20
101	primary school	Breakfast	18
101	primary school	snacks	7
101	high school	Lunch	30
101	high school	Breakfast	40
602	primary school	Lunch	10
602	primary school	Breakfast	21
602	high school	Lunch	25
602	high school	Breakfast	15

Max qty by service in
ID 101
for primary school is 20,
For high shcool is 40 
in ID 602 
for primary school is 21,
For high shcool is 25

Expected measure result is 20+40+21+25 =106 

but the measure show 61

Thanks for very much for your time 

[TomMartens · Accepted solution]

Hey @Anonymous ,

here is a measure that considers more that one grouping columns:

SUMX across MAX using SUMMARIZE = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table'
            , 'Table'[ID School]
            , 'Table'[Level]
        )
        , "MaxQty" , CALCULATE( MAX( 'Table'[Qty] ) )
    )
    , [MaxQty]
)

If I need more than one column inside my iterator table I use SUMMARIZE(...) or table functions that are allowing me to compose tables that are more complex. If there is just one column that determines the table, I use VALUES.

The result:

Hopefully, this provides what you need to tackle your challenge.

So, back to using SUMMARIZECOLUMNS vs SUMMARIZE, depending on your requirements, I would recommend using SUMMARIZECOLUMNS as it is optimized and for this reason, it will perform better, sometimes you will be able to notice this advantage, sometimes you won't, as this will depend on the size of your data.

But, of course, things can become more complex, as the next screenshot shows:

In the 2nd line of visuals I use the measure "SUMX across MAX using SUMMARIZECOLUMNS":

SUMX across MAX using SUMMARIZECOLUMNS = 
SUMX(
    SUMMARIZECOLUMNS(
        'Table'[ID School]
        , 'Table'[Level]
        , "MAXQty" , MAX( 'Table'[Qty] )
    )
    , [MAXQty]
)

The measure works inside the card visual but breaks inside the table visual.

Explaining why it breaks requires more space than is available here and is already done at least to some extent by the article I mentioned in my previous reply to @KNP . Learning also means developing habits by using patterns, habits then will help us to apply the learned things faster. For this reason, I developed the habit to use SUMMARIZE over SUMMARIZECOLUMNS. Knowing that SUMMARIZE is not as fast as SUMMARIZECOLUMNS.

When I work with large datasets and every millisecond counts, I sometimes write measures just for a single visual, these moments are rare and come up with other problems like model complexity.

Regards,

Tom

[KNP]

@TomMartens - Thanks for taking the time to explain. The rules around when to use which in DAX is still a bit of a mystery to me. I need to go spend much more time with Alberto and Marco. 😁

[TomMartens]

Together we learn, togehter we grow!

[Jihwan_Kim]

Result expected measure : =

SUMX ( VALUES ( 'Table'[Level] ), CALCULATE ( MAX ( 'Table'[Qty] ) ) )