Forum Discussion
Helper IIIRecord Count by Date & Employee
I am trying to create a record order column in my table to display the order in which records were created by date and employee.
Sample data:
| Employee Record | Indicent Date | Incident Reference | Record Order |
| A | 01/01/2024 | AAA | 1 |
| A | 02/02/2024 | BBB | 2 |
| A | 03/03/2024 | CCC | 3 |
| B | 01/02/2024 | DDD | 1 |
| B | 02/02/2024 | EEE | 2 |
| C | 03/01/2024 | FFF | 1 |
| C | 04/03/2024 | GGG | 2 |
Looking for a running count for each employee so that I can identify the incident record based on the order they were created.
Please can you advise if this is possible in DAX?
Hi msommerf, there are multiples ways to achieve the desired result. One of them is by using RANKX function:
Here is DAX code with comments explaining what and why we do:Record Order DAX = VAR _CurrentEmployee = 'Table'[Employee Record] //remember employee from the current row RETURN RANKX( //use rank funciton to calcualte index FILTER( //filter table where you need to assing indexes to the current employee 'Table', 'Table'[Employee Record] = _CurrentEmployee ), 'Table'[Indicent Date], //column to base rank on , //unused parameter of RANKX ASC, //order Dense //optional what to do if you have more incidents for the same employee on the same date )Take a look at this article that might be useful in your case - Create group index column by using DAX - Data Cornering
Good luck with your project!
2 Replies
- shafiz_p
Super User
Hi msommerf Try below code:
RecordOrder = VAR CurrentEmp = Employee[Employee Record] VAR CurrentDate = Employee[Indicent Date] RETURN COUNTROWS( FILTER( Employee, Employee[Employee Record] = CurrentEmp && Employee[Indicent Date] <= CurrentDate ) )Desired Output:
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
- Sergii24
Hi msommerf, there are multiples ways to achieve the desired result. One of them is by using RANKX function:
Here is DAX code with comments explaining what and why we do:Record Order DAX = VAR _CurrentEmployee = 'Table'[Employee Record] //remember employee from the current row RETURN RANKX( //use rank funciton to calcualte index FILTER( //filter table where you need to assing indexes to the current employee 'Table', 'Table'[Employee Record] = _CurrentEmployee ), 'Table'[Indicent Date], //column to base rank on , //unused parameter of RANKX ASC, //order Dense //optional what to do if you have more incidents for the same employee on the same date )Take a look at this article that might be useful in your case - Create group index column by using DAX - Data Cornering
Good luck with your project!
