Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Team,
have this question for you guys regarding a sort of index column. I have this (simplified) dataset for you, where the date range is expanded into different rows in order to reflect the daily duration of each product.
Product name | Duration (days) | Duration (night) | Service Start (date) | Operation day (date) | End day (date) |
X1 | 3 | 2 | 10/01/2021 | 10/01/2021 | 12/01/2021 |
X1 | 3 | 2 | 10/01/2021 | 11/01/2021 | 12/01/2021 |
X1 | 3 | 2 | 10/01/2021 | 12/01/2021 | 12/01/2021 |
X1 | 3 | 2 | 20/01/2021 | 20/01/2021 | 22/01/2021 |
X1 | 3 | 2 | 20/01/2021 | 21/01/2021 | 22/01/2021 |
X1 | 3 | 2 | 20/01/2021 | 22/01/2021 | 22/01/2021 |
what do I need to add to this is a sort of index column that counts the days as follows in this example:
Product name | Duration (days) | Duration (night) | Service Start (date) | Operation day (date) | End day (date) | Day no |
X1 | 3 | 2 | 10/01/2021 | 10/01/2021 | 12/01/2021 | 1 |
X1 | 3 | 2 | 10/01/2021 | 11/01/2021 | 12/01/2021 | 2 |
X1 | 3 | 2 | 10/01/2021 | 12/01/2021 | 12/01/2021 | 3 |
X1 | 3 | 2 | 20/01/2021 | 20/01/2021 | 22/01/2021 | 1 |
X1 | 3 | 2 | 20/01/2021 | 21/01/2021 | 22/01/2021 | 2 |
X1 | 3 | 2 | 20/01/2021 | 22/01/2021 | 22/01/2021 | 3 |
what do I get is this
Product name | Duration (days) | Duration (night) | Service Start (date) | Operation day (date) | End day (date) | Day no |
X1 | 3 | 2 | 10/01/2021 | 10/01/2021 | 12/01/2021 | 1 |
X1 | 3 | 2 | 10/01/2021 | 11/01/2021 | 12/01/2021 | 2 |
X1 | 3 | 2 | 10/01/2021 | 12/01/2021 | 12/01/2021 | 3 |
X1 | 3 | 2 | 20/01/2021 | 20/01/2021 | 22/01/2021 | 4 |
X1 | 3 | 2 | 20/01/2021 | 21/01/2021 | 22/01/2021 | 5 |
X1 | 3 | 2 | 20/01/2021 | 22/01/2021 | 22/01/2021 | 6 |
How do i get to the first chart result?
Solved! Go to Solution.
Hi @PhilipTreacy and @ryan_mayu
As i needed to do that at query level (the next step was to merge a query with that column) i created a date difference between Operation day and service start +1 as Custom column in order to achieve "day no" column.
The added column with RANKX was another idea I used as at the very beginning!
thank you for your hints
you can try to create a column
Column = RANKX(FILTER('Table','Table'[Product name]=EARLIER('Table'[Product name])&&'Table'[Service Start (date)]=EARLIER('Table'[Service Start (date)])),'Table'[Operation day (date)],,ASC)
Proud to be a Super User!
Hi @PhilipTreacy and @ryan_mayu
As i needed to do that at query level (the next step was to merge a query with that column) i created a date difference between Operation day and service start +1 as Custom column in order to achieve "day no" column.
The added column with RANKX was another idea I used as at the very beginning!
thank you for your hints
Hi @abartozzi
Which column is being indexed? Service Start or End Day?
regards
Phil
Proud to be a Super User!
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |