Help with Summarize

[hansei]

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

[hansei · Accepted solution]

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

[hansei]

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 )

[Anonymous]

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.

[hansei]

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

[Anonymous]

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 

[hansei]

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

[Anonymous]

Hi @hansei ,

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