topic Re: Help with Summarize in DAX Commands and Tips

Help with Summarize

hansei — Tue, 21 Jan 2020 21:17:26 GMT

I'm having trouble understanding summarize and wonder if someone could help me? If I have a table as:

NAME1	DATE1	NUM1
harry	1/1/2019	11
harry	3/1/2019	22
harry	2/1/2019	33
sally	6/1/2019	100
sally	5/1/2019	200
sally	4/1/2019	300

and i would like to use summarize based on the row with the latest date

NAME1	DATE2	NUM2
harry	3/1/2019	22
sally	6/1/2019	100

the first 2 columns are clear to me as,

NewTable = SUMMARIZE( Table ), Table[NAME1], "DATE2", LASTDATE( Table[DATE1] )

but I am dont' know how to get the corresponding number in the SUMMARIZE function

Re: Help with Summarize

Anonymous — Tue, 21 Jan 2020 22:43:41 GMT

Hi hansei ,

This would be my approach, I sure there are other way to achieve the same 🙂

Table 2 = ADDCOLUMNS(
 SUMMARIZE('Table', 'Table'[NAME1], "LastDate", max('Table'[DATE1])), "Number", 
 var currentName1 = 'Table'[NAME1]
 return
 calculate (sum('Table'[NUM1]), 
 filter('Table', 'Table'[NAME1] = currentName1), 
 filter('Table', 'Table'[DATE1] = [LastDate])))

The Summarize will give you table with names and last dates.
Then you have to calculate / collect the number value from the table based on Name and last date. There can be multiple names with the same last date (at least in theory). That is why both name and last date are used in the calculate to find the correct value.

Hope this helps

jan

Re: Help with Summarize

Anonymous — Wed, 22 Jan 2020 01:36:14 GMT

Why don't you simply create measure which finds max date per user and filter out that visual by adding measure into visual level filter.

Max Date =
CALCULATE (
MAX ( Table1[Date] ),
ALLEXCEPT ( Table1, Table1[name] )
)
Filter=if(max(table1[date])= [max date],1,0)

And set this filter measure to 1 in visual level filter.

Thanks
Pravin

If it resolves your problem mark it as a solution.

Re: Help with Summarize

hansei — Wed, 22 Jan 2020 02:52:51 GMT

Hello Anonymous ,

I'm unsure why, but your solution doesn't seem to filter on LastDate. It aggregates all row entries withe the SUM(). It does the same even when written

filter('Table', 'Table'[NAME1] = currentName1 && 'Table'[DATE1] = [LastDate] )

resulting in e.g. 55 for harry

Re: Help with Summarize

hansei — Wed, 22 Jan 2020 03:01:11 GMT

Hellow Anonymous , the measure is needed when the dataset is consumed. My solution cannot be based on visuals.

Re: Help with Summarize

hansei — Wed, 22 Jan 2020 03:06:04 GMT

While I know I can do this fairly easily in mashup, I thought SUMMARIZE was the way to go. I ended up with the rather inelegant solution of just adding a calculated column a la

Num2 = var vc1 = [Name2] var vltst = [Date2] var vmatches = FILTER( Table, [Name1] = vc1 && [Date1] = vltst ) return FIRSTNONBLANK( SELECTCOLUMNS( vmatches, "whatever", [Num1] ), 1 )

Re: Help with Summarize

Anonymous — Wed, 22 Jan 2020 04:15:06 GMT

Hi hansei ,

That surprises me, since I took your sample data and used it in an empty PBIX:

Re: Help with Summarize

hansei — Wed, 22 Jan 2020 21:14:46 GMT

I ended up with this for my DAX:

T2 = SUMMARIZE( Table, Table[NAME1], "DATE2", LASTDATE( Table[DATE1] ), "NUM2", CALCULATE( FIRSTNONBLANK( Table[NUM1], 1 ), LASTDATE( Table[DATE1] ) ) )

wherein the temporary table returned from LASTDATE propagated filter to orginal Table and then to CALCULATE