Forum Discussion
Need help with DAX tables - Repost with examples
So the situation itself is quite complicated, so please bear with me for a bit. So I have two tables that I merged them together with DAX, this resulting table has informations like Ticket ID, ID RANK, Business Hours for each Ticket in each ID RANK, Classification and many more columns.
So each Ticket has their own ID RANK, and each ID RANK has two dates, the dates that it was first issued and the date that it was closed, the closed date for one ID RANK is the date for the next ID RANK to start, what I did was to expand those dates adding a time stamp to it. So let´s say I have for the Ticket #100 and ID RANK 1 starting at the date 03/03/21 and let´s say the next ID RANK (2) would start at the date 03/05/21, meaning that the ID RANK 1 would end at 03/05/21; So resulting table has a new column which would also include the date of 03/04/21 in between. Now why did I do that? It´s because I need an information about each date, so let´s say the Ticket #100 had in total 21 business hours, but I wanna know at the end of 03/05/21 how many hours it had, at 03/04/21 the same thing and finally at 03/05/21.
Now it comes the issue, I already have a column that calculates the business hour of each issue at the end of each date, but I have a problem with some missing informations. So let´s say the ticket #100 ID RANK 1 started at exactly the beginning of hour work shift at 03/03/21, which means that by the end of the day it should have 9 business hours added to that column. On the day 03/04/21 since we used the whole day, we should also add 9 more hours to it, adding up to 18 total business hours, but on the 03/05/21 let´s say we only worked 3 hours on the issue, which should add up to a total of 21 hours. But the problem begins is that since the ID RANK 2 starts on the same date, the row which contains the date 03/05/21 is already refering to the the ID RANK 2, which means it will start counting hours for ID RANK 2, so instead of showing 21 hours for my ID RANK 1, I have only 18 hours on it.
I tried thinking about other solutions or maybe adding a new line that contains the date of 03/05/21 but for ID RANK 1. But I am having issues on that matter since DAX doesn´t seem to like when we add single lines to our tables. Is there a way for me to fix this issue?
Here is an example of how the table is currently:
And here is how I wish for it to be:
2 Replies
- amitchandakSuper User
EmployeeOM , What I would do in such case, I create a new start Date
New start Date =
coalesce(
maxx(filter(Table, [Ticket] = earlier([Ticket]) && [Rank] = earlier([Rank]) -1) , [End Date])+1, [Start Date])refer if needed
For Business Hours
https://exceleratorbi.com.au/calculating-business-hours-using-dax/
Business Days
Calculating Business Days with and without NETWORKDAYS DAX Function | 2023 Guide: https://www.youtube.com/watch?v=Qs03ZZXXE_
Active Tickets
HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU- EmployeeOMFrequent Visitor
Thanks for your reply! However that did not solve my problem /:
The issue comes that whenever I make those in between dates it just ignores that new date added