Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table showing the number of tickets opened on a given date and also resolved on that same date. What I am trying to find now is the number where the opened date and the resolved date are the same. The output should look similar to below.
Opened | Resolved Same Day | |
1/9/2018 | 5 | 4 |
2/9/2018 | 44 | 39 |
3/9/2018 | 260 | 127 |
4/9/2018 | 214 | 76 |
5/9/2018 | 137 | 53 |
6/9/2018 | 138 | 49 |
7/9/2018 | 99 | 47 |
8/9/2018 | 2 | 2 |
9/9/2018 | 3 | 3 |
I have tried DAX like below
ResolvedSameDay = CALCULATE([Opened],Incident[OpenDate]=Incident[ResolvedDate])
But it finds the OpenDate field but will not accept the ResolvedDate field even though it does exist.
Solved! Go to Solution.
You can use the follwoing measure
ResolvedSameDay = CALCULATE(COUNTA(Incident[OpenDate]),FILTER(Incident,CALCULATE(MAX(Incident[OpenDate]),ALLEXCEPT(Incident,Incident[OpenDate]))=Incident[ResolvedDate]))
I have tested the same and I got the below result
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
You can use the follwoing measure
ResolvedSameDay = CALCULATE(COUNTA(Incident[OpenDate]),FILTER(Incident,CALCULATE(MAX(Incident[OpenDate]),ALLEXCEPT(Incident,Incident[OpenDate]))=Incident[ResolvedDate]))
I have tested the same and I got the below result
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi @andrew_hardwick,
If your problem is resolved with my solution, please mark it as accepted so the thread is closed.
Regards
Affan
Hi,
What does your base data look like?
Something like
SUMMARIZECOLUMNS( Incident[OpenDate], "Calls Opened", COUNT(Incident[OpenDate]), "Call Resolved Same Day", COUNTX( FILTER(Incident, Incident[ResolvedDate] = Incident[OpenDate]), Incident[OpenDate]) )
Hi @HotChilli I tried to create this as a measure and a column, but I got the error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
@HotChilliprovided a complete query.
The expression you want to add as measure is the following:
Incident resolved on the same day = COUNTX( FILTER(Incident, Incident[ResolvedDate] = Incident[OpenDate]) , Incident [OpenDate] ) )
Alternatively, you could also add a new column to your model:
Same day resolution = IF(Incident[ResolvedDate] = Incident[OpenDate]; 1)
... And sum it in a measure.
The DAX i've written creates a new table
Go to Modelling->New Table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |