finding the row with earliest date for every unit and every action in a row

[Anonymous]

I have a Table, I need for every Unit if actions were the same in a row just count the latest action. 

Unit	Date	Action
101	21/05/2021	CLP
101	21/05/2021	CLP
101	22/05/2021	CLP
101	26/05/2021	CLP
101	27/05/2021	CLP
101	27/05/2021	CLP
101	24/08/2021	DR1
101	25/08/2021	DR1
101	25/08/2021	DR1
103	16/06/2021	CLP
103	17/06/2021	CLP
103	17/06/2021	CLP
103	3/08/2021	DR1
103	5/08/2021	DR1
103	5/08/2021	DR1
103	19/08/2021	CLP
103	19/08/2021	CLP

output: add a new column that just put 1 for latest action based on the date and only for the same actions in a row.

Unit	Date	Action	count
101	21/05/2021	CLP	0
101	21/05/2021	CLP	0
101	22/05/2021	CLP	0
101	26/05/2021	CLP	0
101	27/05/2021	CLP	0
101	27/05/2021	CLP	1
101	24/08/2021	DR1	0
101	25/08/2021	DR1	0
101	25/08/2021	DR1	1
103	16/06/2021	CLP	0
103	17/06/2021	CLP	0
103	17/06/2021	CLP	1
103	3/08/2021	DR1	0
103	5/08/2021	DR1	0
103	5/08/2021	DR1	1
103	19/08/2021	CLP	0
103	19/08/2021	CLP	1

Thanks for taking the time, in advance.
@d_gosbell

@amitchandak

[d_gosbell · Accepted solution]

I don't think this is possible in DAX if you have 2 (or more) rows with the identical Unit, Action and Date and want to arbitrarily pick one of them. The only way I could think to split them would be to use Power Query, sort the rows on Unit, Action then Date. Then create an index column and pull out the maximum index per Unit and Action.

eg.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRMjLUNzDVNzIwAnGcfQKUYnWIkTLCLWWGW8qcLCkTfQMLmJRLkCGylCmpUsZAniHQhWaYdoGlzMmRMsZpFW5H4HGeJZIUmhswpGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unit = _t, Date = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit", Int64.Type}, {"Date", type date}, {"Action", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Unit", Order.Ascending},{"Action", Order.Ascending},{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unit", "Action"}, {{"MaxIndex", each List.Max([Index]), type number}}),
    #"Max Index" = #"Added Index",
    #"Merged Queries" = Table.NestedJoin(#"Max Index", {"Unit", "Action"}, #"Grouped Rows", {"Unit", "Action"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"MaxIndex"}, {"MaxIndex"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Custom", each if [Index]=[MaxIndex] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "MaxIndex"})
in
    #"Removed Columns"

If you just want to find the latest date per Unit and Action the answer suggested by @timg would work.

[Anonymous]

Thank you @d_gosbell   and @timg   two solutions worked but for the actions which repeated in a unit just count the latest one and can't separate them.  For example : CLP in Unit = 103 should count twice but in your solution just count latest or earliest one.

Unit	Date	Action	count
101	21/05/2021	CLP	0
101	21/05/2021	CLP	0
101	22/05/2021	CLP	0
101	26/05/2021	CLP	0
101	27/05/2021	CLP	0
101	27/05/2021	CLP	1
101	24/08/2021	DR1	0
101	25/08/2021	DR1	0
101	25/08/2021	DR1	1
103	16/06/2021	CLP	0
103	17/06/2021	CLP	0
103	17/06/2021	CLP	1
103	3/08/2021	DR1	0
103	5/08/2021	DR1	0
103	5/08/2021	DR1	1
103	19/08/2021	CLP	0
103	19/08/2021	CLP	1

[d_gosbell · Accepted solution]

Your requirements are not clear. 

---

@Anonymous wrote:

... but for the actions which repeated in a unit just count the latest one and can't separate them.  For example : CLP in Unit = 101 should count twice but in your solution just count latest or earliest one.

---

My solution should match the output of the "count" column which you posted in your original question where only 1 of the duplicate rows was counted.

While the solution by @timg will produce a column with a 1 for each of the duplicated rows, so if you create a simple SUM() based measure over this column you will get the total count of any duplicates.

If either of the above does not meet your requirements can you post an updated version of the actual output you desire?

[d_gosbell · Accepted solution]

I don't think this is possible in DAX if you have 2 (or more) rows with the identical Unit, Action and Date and want to arbitrarily pick one of them. The only way I could think to split them would be to use Power Query, sort the rows on Unit, Action then Date. Then create an index column and pull out the maximum index per Unit and Action.

eg.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRMjLUNzDVNzIwAnGcfQKUYnWIkTLCLWWGW8qcLCkTfQMLmJRLkCGylCmpUsZAniHQhWaYdoGlzMmRMsZpFW5H4HGeJZIUmhswpGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unit = _t, Date = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit", Int64.Type}, {"Date", type date}, {"Action", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Unit", Order.Ascending},{"Action", Order.Ascending},{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unit", "Action"}, {{"MaxIndex", each List.Max([Index]), type number}}),
    #"Max Index" = #"Added Index",
    #"Merged Queries" = Table.NestedJoin(#"Max Index", {"Unit", "Action"}, #"Grouped Rows", {"Unit", "Action"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"MaxIndex"}, {"MaxIndex"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Custom", each if [Index]=[MaxIndex] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "MaxIndex"})
in
    #"Removed Columns"

If you just want to find the latest date per Unit and Action the answer suggested by @timg would work.

[Anonymous]

@timg Could you please have a look at my question?

[timg]

Hi Zara, 

Thnx for tagging! There's probably multiple ways to get to the result but here's an example in which I use a calculated column:

Ind_MinDate = 
VAR MinDateUnit =
    FORMAT (
        CALCULATE (
            MIN ( 'Table'[Date] ),
            FILTER ( 'Table', 'Table'[Unit] = EARLIER ( 'Table'[Unit] ) )
        ),
        "DD-MM-YYYY"
    )
RETURN
    IF ( MinDateUnit = FORMAT ( 'Table'[Date], "DD-MM-YYYY" ), 1, 0 )

Basically you first use a variable to calculate the minimum date per unit, and then you match that date with the date in the Date column and return a 1 if they are equal (example below).

Hope that helps!

Regards,

Tim