Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I try to simplify my problem, I hope it's understandable:
I got a table with Columns Ticket_Id, User, Date, Hours. It should be possible to filter the table by Ticket_Id and date.
First, I want to calculate the sum of hours spent on all tickets a day while paying attention to the ticket filter.
Then, for each User, I want to calculate the maximum of that sum in the selected date period.
It would be no problem if I could just calculate the sum of hours a user spent on all tickets for each day in a calculated column. The problem is that this sum needs to depend on the ticket filter, which can be set at any time. Thus a measure is necessary to make it dynamic I think?
So far I would calculate the column
SumT = CALCULATE(SUM(Abfrage1[Hours]);FILTER(ALLSELECTED(Abfrage1);Abfrage1[Date]=Earlier(Abfrage1[Date]) && Abfrage1[User]=EARLIER(Abfrage1[User])))
and then take the maximum of that.
MaxT = CALCULATE(MAX(Abfrage1[SumT]);FILTER(ALLSELECTED(Abfrage1); Abfrage1[User]=EARLIER(Abfrage1[User])))
The problem is that this sum does not take into calculation the tickets and dates that I use as a filter. Thus I think a measure is necessary but that doesn't allow the Earlier function.
In addition to all this, I also need to be able to use the MaxT value for every user as a filter criterium on a report page.
Solved! Go to Solution.
Hi @Elli91,
Could you try the formula below to see if it works in your scenario?
Measure = CALCULATE ( MAXX ( SUMMARIZE ( Abfrage1; Abfrage1[User]; Abfrage1[Date]; "TotalHours"; SUM ( Abfrage1[Hours] ) ); [TotalHours] ); ALL ( Abfrage1[Ticket_Id] ) )
Regards
Hi @Elli91,
If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the MaxT, and then use the measure as Visual Level Filter for all visuals on your report to filter Users in your scenario.
Measure = MAXX ( SUMMARIZE ( Abfrage1; Abfrage1[User]; Abfrage1[Date]; "TotalHours"; SUM ( Abfrage1[Hours] ) ); [TotalHours] )
Regards
Hi @v-ljerr-msft,
thanks for your reply! It's already pretty close to what I need, however, there is still a problem. I want to display a matrix with columns date, a hierarchy of User and Tickets as rows and the sum of hours as a value.
I need the measure to filter for users. That way, I want to find all of the users that have spent more than X hours (I'll define X in the filter) a day on all selected tickets (summed up). Your measure gives me just that, however, with the measure as a filter I can't see all of the selected tickets the user has worked on in my selected time period. Instead, only the tickets that the user has worked on on a day with a total of more than X hours are displayed. Do you have any idea how I can see all of the tickets here?
Edit: In addition to that, I can't see users with a total of more than X hours a day if their tickets have less than X hours each. I can see the missing Users if I remove the hierarchy User - Tickets and only display Users in the rows. But I really need the drill down to tickets.
Hi @Elli91,
Could you try the formula below to see if it works in your scenario?
Measure = CALCULATE ( MAXX ( SUMMARIZE ( Abfrage1; Abfrage1[User]; Abfrage1[Date]; "TotalHours"; SUM ( Abfrage1[Hours] ) ); [TotalHours] ); ALL ( Abfrage1[Ticket_Id] ) )
Regards
@v-ljerr-msft
Thank you again! At first I thought it worked perfectly, but after checking some more Users I discovered that the total amount of hours is correct (it wasn't before). However, when I drill down, it still doesn't show all of the tickets and the hours don't add up to the user's total amount of hours anymore.
Hi @Elli91,
Could you post some screenshots with explanation on the issue? It's better to share a sample pbix file which can reproduce the issue, so that I can further assist on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |