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

[JustinDoh1]

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.

[JustinDoh1]

@v-shex-msft 

I am trying to follow up on my previous question.

Thanks.

[daXtreme]

@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.

[technolog · Accepted solution]

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.

[JustinDoh1]

@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?

[v-shex-msft]

HI @JustinDoh1,

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

Regards,

Xiaoxin Sheng

[JustinDoh1]

@v-shex-msft 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?

[technolog]

It will be something like this:

SELECT [Item], SUM([Value])

FROM 'Table'

GROUP BY [Item]