Forum Discussion

estgas's avatar
estgas
Frequent Visitor
8 years ago
Solved

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

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

     

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

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