topic Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX) in DAX Commands and Tips

Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

JustinDoh1 — Sun, 17 Jul 2022 00:25:54 GMT

I am trying to analyze this DAX code (indicated on the bottom link)

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

I am just not getting what "_Value" is all about.

According to the definition of Summarize, it "creates a summary of the input table grouped by the specified columns."

This is definition by Microsoft site:

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

So, I am guessing SUM('Table'[Value]) is a <groupBy_columnName>, and then what does "_Value" do?

How do we express Ex1 and Ex2 in SQL?

Thanks.

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

technolog — Sun, 17 Jul 2022 08:11:09 GMT

Hi!

In Ex1 it's the name of created column. In this column calculate SUM( Table[Value] )

In Ex2 it's the same.

[___Value] it's just a created column name.

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

daXtreme — Sun, 17 Jul 2022 08:38:46 GMT

JustinDoh1

By the way, SUMMARIZE should never be used the way it is in your code in measures. There is a famous article by Marco Ruso and Alberto Ferrari on www.sqlbi.com that explains why and what to replace it with.

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

JustinDoh1 — Sun, 17 Jul 2022 23:34:48 GMT

technolog

Thank you for your help.

Just for reference:

Objective of Summarzie: returns summary table for the requested totals over a set of groups.

So, how do we interpret the bottom DAX in English or SQL?

SUMMARIZE(
            'Table',
            [Item],
            "__Value",SUM('Table'[Value])
        )

It returns [Item] from 'Table' for sum of value?

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

Anonymous — Thu, 21 Jul 2022 06:08:05 GMT

HI JustinDoh1,

This formula mean get the Item field and summary values from your table based on the Item group.

Regards,

Xiaoxin Sheng

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

JustinDoh1 — Tue, 26 Jul 2022 22:23:37 GMT

Anonymous Thank you for your reply.

I am not quite understood.

"get the Item field and summary values from your table based on the Item group"

How do we convert this into SQL?

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

JustinDoh1 — Mon, 01 Aug 2022 23:35:00 GMT

Anonymous

I am trying to follow up on my previous question.

Thanks.

Re: Trying to understand Expression (in SUMMARIZE & MINX/MAXX)

technolog — Tue, 02 Aug 2022 11:38:37 GMT

It will be something like this:

SELECT [Item], SUM([Value])

FROM 'Table'

GROUP BY [Item]