The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi community,
Lets say my data are sales, employees and dates.
I would like to create a column where I will have a count of dates whith transactions, that will be irrelevant from the amount of the transactions, but specific for employees.
Every employee has a different working schedule, and they do not work the same amount of days each week, month etc.
I want to able to see how each employee performed based on their working days. So, I would like to create a slicer with options like "First 30 Days", "First 100 Days" etc. In order to do so I need this ranking column.
So this is what I would like to achieve:
Date Time | Sales ID | Employee Name | Sales Working Date Count |
1.1.22 12:00PM | 1 | John | 1 |
1.1.22 13:00PM | 2 | Jack | 1 |
1.1.22 13:00PM | 3 | John | 1 |
1.1.22 14:00PM | 4 | Mary | 1 |
2.1.22 13:00PM | 5 | John | 2 |
2.1.22 14:00PM | 6 | Mary | 2 |
2.1.22 15:00PM | 7 | Mary | 2 |
3.1.22 11:00AM | 8 | Jack | 2 |
3.1.22 12:00PM | 9 | Mary | 3 |
3.1.22 16:00PM | 10 | Mary | 3 |
4.1.22 11:00AM | 11 | Mary | 4 |
4.1.22 12:00PM | 12 | Jack | 3 |
4.1.22 16:00PM | 13 | Jack | 3 |
I would prefer the best solution for this performance wise either in Power Query or as a calculated column.
In MySQL it could be even better...
Solved! Go to Solution.
Adding a date column as a helper column make it much easier; but that's no fun at all. I avoid it on purpose for fun only.
PQ solution,
Caculated Column solution,
Stay tuned for SQL solution after my lunch break ...
=========================================
The most concise solution (T-sql),
Bonus solution by powerful Excel formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Adding a date column as a helper column make it much easier; but that's no fun at all. I avoid it on purpose for fun only.
PQ solution,
Caculated Column solution,
Stay tuned for SQL solution after my lunch break ...
=========================================
The most concise solution (T-sql),
Bonus solution by powerful Excel formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @CNENFRNL,
one more question. If we want to add one more column in the grouping.
For example we rank the days by Employee and Sales, what if we wanted a Store column as well. Meaning different rank for each store.
I have managed to do it in DAX, but having some difficulties in M and SQL.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |