Forum Discussion

EmployeeOM's avatar
EmployeeOM
Frequent Visitor
2 years ago

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