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.
Hi, hopefully someone can help with this.
I have a dataset that looks at tickets created and closed on a service desk. Both created date and closed date are joined to a calendar table.
What I want to be able to do is create a measure which counts number of tickets created AND closed in the selected period. For example, a user selects June 2018 and it will count all tickets that were created in june 2019 and solved in June 2019. Any without a solve date or a solve date higher than june 2019 won't show up, and neither will tickets created before or after this period. If the user then selects the whole of 2018 - it will apply this same logic to the entire year.
Does this make sense? Hopefully someone can help
Thanks!
Solved! Go to Solution.
HI @Anonymous ,
Assum that we have a table like below:
Then first we need to create a calendar table for slicer like below without relationship between above table:
Table = CALENDAR(DATE(2018,01,01),DATE(2019,12,31))
After that, we can create a measure using the following DAX query:
count_ticket = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALL ( Table1 ), Table1[createddate] >= MIN ( 'Table'[Date] ) && Table1[createddate] <= MAX ( 'Table'[Date] ) && Table1[closeddate] >= MIN ( 'Table'[Date] ) && Table1[closeddate] <= MAX ( 'Table'[Date] ) ) )
The drag the measure to a card to display and drag the calendar to a slicer, the result will like below:
Best Regards,
Teige
HI @Anonymous ,
Assum that we have a table like below:
Then first we need to create a calendar table for slicer like below without relationship between above table:
Table = CALENDAR(DATE(2018,01,01),DATE(2019,12,31))
After that, we can create a measure using the following DAX query:
count_ticket = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALL ( Table1 ), Table1[createddate] >= MIN ( 'Table'[Date] ) && Table1[createddate] <= MAX ( 'Table'[Date] ) && Table1[closeddate] >= MIN ( 'Table'[Date] ) && Table1[closeddate] <= MAX ( 'Table'[Date] ) ) )
The drag the measure to a card to display and drag the calendar to a slicer, the result will like below:
Best Regards,
Teige
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |