cancel
Showing results for 
Search instead for 
Did you mean: 
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


View solution in original post

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors