Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@OsmanEmi , Create a new column like
countx(filter(Table, [Restaurant ID] = earlier([Restaurant ID]) && [Delivery Date] >=earlier([Delivery Date]) ),[Restaurant ID])
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.
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.
@OsmanEmi , Create a new column like
countx(filter(Table, [Restaurant ID] = earlier([Restaurant ID]) && [Delivery Date] >=earlier([Delivery Date]) ),[Restaurant ID])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |