The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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 )
Hellow @Anonymous , the measure is needed when the dataset is consumed. My solution cannot be based on visuals.
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
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
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
14 | |
12 | |
12 | |
7 |