How to compare the rows in the same table and create a separate column in power query or DAX?

[slru]

In the example table below:

There are rows with similar IDs but the dates associated with them is different.  My goal is to count the En, Gd, Wd, Gd1 and Wd1 columns for each ID but when there are multiple rows with same ID, I want to retain the one with the latest date. Example, I want to count row 1 with id 15224, date 5/31/2017, Gd1 as 1 and not count the row 2 with id 15224, date 9/26/2016 and wd as 1. How do I do this in power query or using a measure ? Thank you and I appreciate your help.   

ID	Date	En	Gd	Wd	Gd1	Wd1
15224	5/31/2017	0	1	0	0	0
15224	9/26/2016	0	0	1	0	0
15254	5/31/2017	0	1	0	0	0
15254	6/6/2017	0	0	1	0	0
14415	5/31/2017	0	1	0	0	0
14415	8/18/2015	0	0	0	0	0
14602	12/21/2018	0	0	0	1	0
14602	5/31/2017	0	1	0	0	0
15262	5/17/2018	0	0	0	1	0
15262	5/31/2017	0	0	1	0	0
15306	12/21/2018	0	0	0	1	0
15306	5/31/2017	0	1	0	0	0

[sevenhills · Accepted solution]

It wasn't clear what is the output expected. Are you looking for each ID latest date or a way to identify the latest record?

I provided both DAX and power query ways and tweak to your needs!

Say, if you want only latest records, filter "Latest ID Record" as 1.

Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBLDoAgDEXRvTA2Ka/Qgmsx7H8b8ouKmsCggcHJTeE4DITZm80IORBbhHy3edDPNmm75U6sRepD4C1luVmlkj7hT9J7yFqyy0iIRcogRqmWS4OJazS+GD506UXaJMKkecmx+fefzuriop1OFk0n", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, En = _t, Gd = _t, Wd = _t, Gd1 = _t, Wd1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"En", Int64.Type}, {"Gd", Int64.Type}, {"Wd", Int64.Type}, {"Gd1", Int64.Type}, {"Wd1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Latest Date by ID", each List.Max([Date]), type nullable date}, {"allRows", each _, type table [ID=nullable number, Date=nullable date, En=nullable number, Gd=nullable number, Wd=nullable number, Gd1=nullable number, Wd1=nullable number]}}),
    #"Expanded allRows" = Table.ExpandTableColumn(#"Grouped Rows", "allRows", {"Date", "En", "Gd", "Wd", "Gd1", "Wd1"}, {"Date", "En", "Gd", "Wd", "Gd1", "Wd1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded allRows", "Latest ID Record", each if ( [Latest Date by ID] = [Date]) then 1 else 0, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ID", "Date", "En", "Gd", "Wd", "Gd1", "Wd1", "Latest Date by ID", "Latest ID Record"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", Int64.Type}, {"Date", type date}, {"En", Int64.Type}, {"Gd", Int64.Type}, {"Wd", Int64.Type}, {"Gd1", Int64.Type}, {"Wd1", Int64.Type}, {"Latest Date by ID", type date}, {"Latest ID Record", Int64.Type}})
in
    #"Changed Type1"

Output:

DAX 

Say, your data looks as below:

Measures:

Latest Date by ID = CALCULATE( Max( TableRank_Dups[Date]), FILTER(ALLSELECTED(TableRank_Dups), TableRank_Dups[ID] = max(TableRank_Dups[ID])))


Latest ID Record = 
IF ( HASONEVALUE( TableRank_Dups[ID]) && HASONEVALUE(TableRank_Dups[Date]) ,
 IF (Max( TableRank_Dups[Date]) = [Latest Date by ID] , 1, 0)
 , blank())

/*
IF ( HASONEVALUE( TableRank_Dups[ID]) && HASONEVALUE(TableRank_Dups[Date]) ,
 IF (Max( TableRank_Dups[Date]) = CALCULATE( Max( TableRank_Dups[Date]), FILTER(ALLSELECTED(TableRank_Dups), TableRank_Dups[ID] = max(TableRank_Dups[ID]))), 1, 0)
 , blank())
 */

Output:

[slru]

Thank you very much for your help. I appreciate it.

[slru]

Nevermind,  the formula below fixed the dates. Thanks.

Latest_Date_by_ID = CALCULATE ( Max( 'Table'[Date]), ALLEXCEPT ( 'Table', 'Table'[ID] ) )

[slru]

Thank you for quick response. Sorry, my explanation is not clear.  I will try again. This is a sample table I am looking at.

ID             Date                 En   Gd   Wd   Gd1  Wd1

--------------------------------------------------------------
15224      5/31/2017         0     1      0      0       0
15224      9/26/2016         0     0      1      0       0
15254      5/31/2017         0     1      0      0       0
15254      6/6/2017           0     0      1      0       0
14415      5/31/2017         0     1      0      0       0
14415      8/18/2015         0     0      0      0       0
14602      12/21/2018       0     0      0      1       0
14602      5/31/2017         0     1      0      0       0
15262      5/17/2018         0     0      0      1       0
15262      5/31/2017         0     0      1      0       0
15306      12/21/2018       0     0      0      1       0
15306       5/31/2017        0     1      0      0       0

My goal is to get a final total count of En, Gd, Wd, Gd1, Wd1. There are rows with same IDs but with different Date. In my calculation, I want to include rows with latest Date for each ID.  

The answer would be En = 0, Gd = 3, Wd = 0, Gd1 = 3

Gd = 3 because I have counted row 1, 3, 5.  Row 8 and 12 are not counted.

    Row 7 and 8 have same ID, but row 7 has the latest Date. So, row 8 is not included

    Row 11 and row 12 has same ID, but row 11 has latest date. So, row 12 is not included

Wd = 0 because none of the rows passed the test.

   Row 1 & 2 has the same ID but Row has the latest Date. So Row 2 is not included.

   Row 3 & 4 has the same ID but Row has the latest Date. So Row 4 is not included.

Hope this clarifies. Thanks.

[sevenhills · Accepted solution]

I think you can use either DAX or Power Query way like I provided.

In the table visualization, filter those only with "Latest ID Record" as 1.

Or you want to use in a measure, you can do this as :

Measure = 

var _t = Filter( ALLSELECTED(TableRank_Dups), [Latest ID Record] = 1)

RETURN CALCULATE( Sum(TableRank_Dups[En]) + Sum(TableRank_Dups[Gd]) + Sum(TableRank_Dups[Wd]) + sum(TableRank_Dups[Gd1]) + sum(TableRank_Dups[Wd1]) , _t)

Say, if you want individual measures, You can modify this measure logic for your needs. 

Tips:

It is a common scenario.

Say, if you have many rows, I will rather create in Power Query than doing in DAX.

Say, if you want ease of use or some large number of rows, I will create another table in Power Query as latest records table and filter only those with latest records.

Thanks

[sevenhills · Accepted solution]

It wasn't clear what is the output expected. Are you looking for each ID latest date or a way to identify the latest record?

I provided both DAX and power query ways and tweak to your needs!

Say, if you want only latest records, filter "Latest ID Record" as 1.

Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBLDoAgDEXRvTA2Ka/Qgmsx7H8b8ouKmsCggcHJTeE4DITZm80IORBbhHy3edDPNmm75U6sRepD4C1luVmlkj7hT9J7yFqyy0iIRcogRqmWS4OJazS+GD506UXaJMKkecmx+fefzuriop1OFk0n", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, En = _t, Gd = _t, Wd = _t, Gd1 = _t, Wd1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"En", Int64.Type}, {"Gd", Int64.Type}, {"Wd", Int64.Type}, {"Gd1", Int64.Type}, {"Wd1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Latest Date by ID", each List.Max([Date]), type nullable date}, {"allRows", each _, type table [ID=nullable number, Date=nullable date, En=nullable number, Gd=nullable number, Wd=nullable number, Gd1=nullable number, Wd1=nullable number]}}),
    #"Expanded allRows" = Table.ExpandTableColumn(#"Grouped Rows", "allRows", {"Date", "En", "Gd", "Wd", "Gd1", "Wd1"}, {"Date", "En", "Gd", "Wd", "Gd1", "Wd1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded allRows", "Latest ID Record", each if ( [Latest Date by ID] = [Date]) then 1 else 0, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ID", "Date", "En", "Gd", "Wd", "Gd1", "Wd1", "Latest Date by ID", "Latest ID Record"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", Int64.Type}, {"Date", type date}, {"En", Int64.Type}, {"Gd", Int64.Type}, {"Wd", Int64.Type}, {"Gd1", Int64.Type}, {"Wd1", Int64.Type}, {"Latest Date by ID", type date}, {"Latest ID Record", Int64.Type}})
in
    #"Changed Type1"

Output:

DAX 

Say, your data looks as below:

Measures:

Latest Date by ID = CALCULATE( Max( TableRank_Dups[Date]), FILTER(ALLSELECTED(TableRank_Dups), TableRank_Dups[ID] = max(TableRank_Dups[ID])))


Latest ID Record = 
IF ( HASONEVALUE( TableRank_Dups[ID]) && HASONEVALUE(TableRank_Dups[Date]) ,
 IF (Max( TableRank_Dups[Date]) = [Latest Date by ID] , 1, 0)
 , blank())

/*
IF ( HASONEVALUE( TableRank_Dups[ID]) && HASONEVALUE(TableRank_Dups[Date]) ,
 IF (Max( TableRank_Dups[Date]) = CALCULATE( Max( TableRank_Dups[Date]), FILTER(ALLSELECTED(TableRank_Dups), TableRank_Dups[ID] = max(TableRank_Dups[ID]))), 1, 0)
 , blank())
 */

Output:

[slru]

Latest Date by ID = CALCULATE( Max( TableRank_Dups[Date]), FILTER(ALLSELECTED(TableRank_Dups), TableRank_Dups[ID] = max(TableRank_Dups[ID])))

Thank you. I am trying DAX method. This line is returning the same date 12/21/2018 for every row in the table instead of returning the latest date for each ID as shown in your output. What am I missing? Thanks.