Creating counters in more than one instance

[OsmanEmi]

Hi,

I have the below table. I would like the counter coulmn to count based on dates at the same restaurant.

I cant's seem to wrap my head around how this would be done using a measure or a calculated column (preferably calculated column). Any help would be appreciated.

Restaurant ID	Delivery Date	Counter
1	27/11/2020	1
2	27/11/2020	1
3	27/11/2020	1
1	26/11/2020	2
1	25/11/2020	3
2	25/11/2020	2
3	25/11/2020	2
1	24/11/2020	4

[amitchandak · Accepted solution]

@OsmanEmi , Create a new column like

countx(filter(Table, [Restaurant ID] = earlier([Restaurant ID]) && [Delivery Date] >=earlier([Delivery Date]) ),[Restaurant ID])

[Anonymous · Accepted solution]

Hi @OsmanEmi 

 amitchandak 's reply use countx funtion to achieve your goal, and you can try my way to use rankx function to build a calculated column or use M query to build a column in Power Query Editor.

Calculated column:

C.Counter = RANKX(FILTER('Table','Table'[Restaurant ID]=EARLIER('Table'[Restaurant ID])),'Table'[Delivery Date],,DESC)

M query:

You need sort ID column by asc and sort Date column by Desc, group by ID column (Operation = ALLROWS)

Add a custom column , reomve other columns and expand custom column.

Custom = Table.AddIndexColumn([AllROWS],"Rank",1,1)

Mquery in Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1zc01DcyMDJQitWJVjLCFDLGFAJrNMMUMsU0yxTTLFNMjSYIoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Restaurant ID     " = _t, #"   Delivery Date                    " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Restaurant ID     ", Int64.Type}, {"   Delivery Date                    ", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"   Delivery Date                    ", "Delivery Date"}, {"Restaurant ID     ", "Restaurant ID"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Restaurant ID", Order.Ascending}, {"Delivery Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Restaurant ID"}, {{"AllROWS", each _, type table [Restaurant ID=nullable number, Delivery Date=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllROWS],"Rank",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Restaurant ID", "AllROWS"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Restaurant ID", "Delivery Date", "Rank"}, {"Custom.Restaurant ID", "Custom.Delivery Date", "Custom.Rank"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Rank", "Counter"}}),
    #"Sorted Rows1" = Table.Sort(#"Renamed Columns1",{{"Custom.Delivery Date", Order.Descending}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows1",{{"Counter", "MQ.Counter"}, {"Custom.Restaurant ID", "Restaurant ID"}, {"Custom.Delivery Date", "Delivery Date"}})
in
    #"Renamed Columns2"

Result is as below.

You can download the pbix file from this link: Creating counters in more than one instance

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@OsmanEmi , Create a new column like

countx(filter(Table, [Restaurant ID] = earlier([Restaurant ID]) && [Delivery Date] >=earlier([Delivery Date]) ),[Restaurant ID])