SummarizeColumns with FirstNonBlank

[estgas]

Hi! I would like to create a new table that summarizes Prod. and Cust. depending on last Date (for both Prod. and Cust).

Please see an example below.

For the second Prod. there is the same date for different Cust. that's why I thought to use FirstNonBlank.

Prod.	Cust.	Date
1	a	dt1
1	a	dt2
1	b	dt3
2	a	dt1
2	a	dt4
2	c	dt4

Desired result in New Table:

Prod.	Cust.	Date (max)
1	b	dt3
2	a	dt4

Could anyone please help me? Many thanks in advance, Kind regards, est gas

[Zubair_Muhammad · Accepted solution]

Hi @estgas

Go to Modelling Tab>>>>NEW TABLE

New Table =
SUMMARIZE (
    TableName,
    TableName[Prod.],
    "Last Date", MAX ( TableName[Date] ),
    "Last Date Customers", CALCULATE (
        FIRSTNONBLANK ( TableName[Cust.], TableName[Cust.] ),
        FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) )
    )
)

[Zubair_Muhammad · Accepted solution]

Hi @estgas

Go to Modelling Tab>>>>NEW TABLE

New Table =
SUMMARIZE (
    TableName,
    TableName[Prod.],
    "Last Date", MAX ( TableName[Date] ),
    "Last Date Customers", CALCULATE (
        FIRSTNONBLANK ( TableName[Cust.], TableName[Cust.] ),
        FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) )
    )
)

[Zubair_Muhammad · Accepted solution]

@estgas

[estgas]

Many Thank Zubair! Exactly what I was looking for!

By the way I replaced SUMMARIZE with SUMMARIZECOLUMNS assuming that is preferred according to conclusion of  https://www.sqlbi.com/articles/introducing-summarizecolumns/

[Zubair_Muhammad]

HI @estgas

Yes I believe

SummarizeColumns

or

combination of Summarize with AddColumns

is much faster than mere Summarize