Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
estgas
Frequent Visitor

SummarizeColumns with FirstNonBlank

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
1adt1
1adt2
1bdt3
2adt1
2adt4
2cdt4

 

 

Desired result in New Table:

Prod.Cust.Date (max)
1bdt3
2adt4

 

 

 

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

 

 

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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] ) )
    )
)

 


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

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] ) )
    )
)

 


Regards
Zubair

Please try my custom visuals

@estgas

 

61.png

 


Regards
Zubair

Please try my custom visuals

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/

 

HI @estgas

 

Yes I believe

 

SummarizeColumns

or

combination of Summarize with AddColumns

 

is much faster than mere Summarize


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.