Create a new data with Max of record

[sunny27]

Hi,

How can we summarize the following data?

EmployeeNumber	Status	Date
100	1	1/1/2017
100	2	2/1/2017
100	3	3/1/2017
100	4	4/1/2017
101	1	4/25/2017
101	2	5/25/2017
102	2	3/10/2017
103	2	4/10/2017
104	1	5/10/2017
105	1	6/10/2017
106	1	7/10/2017
106	2	8/10/2017
106	3	9/10/2017
106	4	10/10/2017

I want to display max(date) for the Employees. In this case the output will be:

EmployeeNumber	Status	Date
100	4	4/1/2017
101	2	5/25/2017
102	2	3/10/2017
103	2	4/10/2017
104	1	5/10/2017
105	1	6/10/2017
106	4	10/10/2017

How can we do this? I tried to summarize and Group by but it's not working.
I want to create a summary table that does this. So that I can use my measures to on top that summary table. Is there a way to create a new calculated table in Power BI to achieve the above result?

Using matrix visual will work but in that case I cannot create my own measures to leverage that data. I appreciate your time. Thanks!

[v-jiascu-msft]

Hi @sunny27,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[v-jiascu-msft]

Hi @sunny27,

The formula below will generate a calculated table. Please give it try. I assumed the orders of status and date are accordingly.

Table =
SUMMARIZE (
    'Table1',
    Table1[EmployeeNumber],
    "maxStatus", MAX ( 'Table1'[Status] ),
    "maxDate", MAX ( 'Table1'[Date] )
)

Best Regards,

Dale

[Ashish_Mathur]

Hi,

Share the final result you are expecting.  It could be possible that a formula can be written to get your result directly from the input Table.

[Anonymous]

Hi @sunny27

Create a measure in DAX:

Max in group = IF(CALCULATE(MAX('Table1'[Date]),ALLEXCEPT('Table1','Table1'[EmpNo]))=MAX('Table1'[Date]),1,0)

and then apply filter for this measure to 1 as visual filter.

This calculation doesnt load into model, so its memory / performance effective.

Thanks
Raj

[sunny27]

Hi Rajendran,

Thanks for replying but here is the thing, Is there a way to create a measure or Calculated field to find the latest status of a facility by Date.I tried using [Max in group] measure and tried filtering in visual but no use.

Thanks!

[Interkoubess]

Hi @sunny27,

I created 2 simple measures ( for date and Status) and I put them in my report (Table), it seems to works

M_Status = CALCULATE(MAX(Data[Status]))

M_Date = CALCULATE(MAX(Data[Date]))

Hope it helps...

Ninter

[Anonymous]

Goto Edit Queries

and Try this