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 think this is proably quite straightforward, but I can't seem to get my head around it.
I want to create an index column that indicates the occurence of that value in the column sorted by date
Date | Unit ID | Index |
01/01/2017 | 101 | 1 |
02/01/2017 | 101 | 2 |
03/01/2017 | 121 | 1 |
04/01/2017 | 111 | 1 |
05/01/2017 | 111 | 2 |
06/01/2017 | 121 | 2 |
07/01/2017 | 101 | 3 |
08/01/2017 | 121 | 3 |
any help would be greatly appreciated
regards
Guy
Solved! Go to Solution.
You can group on Unit ID with operation "All Rows".
Next adjust the generated to have an Index added to the nested tables while grouping.
Then expand and sort on date.
let Source = Table1, Grouped = Table.Group(Source, {"Unit ID"}, {{"AllData", each Table.AddIndexColumn(_,"Index",1,1), type table}}), Expanded = Table.ExpandTableColumn(Grouped, "AllData", {"Date", "Index"}, {"Date", "Index"}), Reordered = Table.ReorderColumns(Expanded,{"Date", "Unit ID", "Index"}), Sorted = Table.Sort(Reordered,{{"Date", Order.Ascending}}) in Sorted
If the data is not sorted on date and you want to get the original sort back, then you can add an Index at the first step (before grouping - call this index e.g. "OriginalSort" to prevent duplicate name "Index") and at the end sort on the OriginalSort and remove this.
Measure/Column=CALCULATE(RANKX(ALL('Table'[Unit ID]),CALCULATE(SUM('Table'[Unit ID]))),ALLEXCEPT(Table,Table[Date],Table[Unit ID]))
Hi @Guy
Follow the link
https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864
where @Imekf provided a solution to similar problem.
The solution is adding the following lines in the queryeditor.
let
Source = Table1,
Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
#"Expanded Partition"
Using this and the data provided I could get what you wanted.
If the linked in article solves your issue kindly accept this as a solution and also give KUDOS to me and @Imekf
Cheers
CheenuSing
@CheenuSing that looks like you confirm my solution, so thanks!
You can group on Unit ID with operation "All Rows".
Next adjust the generated to have an Index added to the nested tables while grouping.
Then expand and sort on date.
let Source = Table1, Grouped = Table.Group(Source, {"Unit ID"}, {{"AllData", each Table.AddIndexColumn(_,"Index",1,1), type table}}), Expanded = Table.ExpandTableColumn(Grouped, "AllData", {"Date", "Index"}, {"Date", "Index"}), Reordered = Table.ReorderColumns(Expanded,{"Date", "Unit ID", "Index"}), Sorted = Table.Sort(Reordered,{{"Date", Order.Ascending}}) in Sorted
If the data is not sorted on date and you want to get the original sort back, then you can add an Index at the first step (before grouping - call this index e.g. "OriginalSort" to prevent duplicate name "Index") and at the end sort on the OriginalSort and remove this.
Thanks for the response, it did fully answer the question I asked so it;s my fault for not being more specific. I had hoped to do this through a calculated column in DAX rather than through the query editor as the table itself is a calculated table. Is this possible?
thanks
Guy
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |