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 list of service tickets that were logged with their date and time. Alongside is their Severity (level 1-2-3-4). I'm trying to figure out a dax formula to count the number of days withing a current month where we had no tickets of severity 4. I could post what I have now as a formula but it's clearly wrong as it's telling me last month had 174 days without severity 4 tickets. Any help would be welcomed.
Solved! Go to Solution.
Hi @GuillaumeB,
Based on my test, you could refer to below formula:
Measure = CALCULATE(COUNT(Table1[Ticket date]),FILTER('Table1','Table1'[Ticket security level]=1||'Table1'[Ticket security level]=2||'Table1'[Ticket security level]=3),FILTER('Table1','Table1'[Ticket flag]=1))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Try DISTINCTCOUNT instead of COUNT in the provided pattern.
Hi @GuillaumeB
Please share data / data model / pbix on google drive /onedrive and paste the link here.
Cheers
CheenuSing
@CheenuSing I cannot share anything unfotunately as it contains sensitive client information. Here is some info I can share that can help.
In the data model I have a relationship between Calendar Date and Ticket date.
Within Ticket table I have the following relevant fields:
Ticket Date
Ticket Severity level
Ticket Flag (flag used to mark if they should be counted based on other scenarios, 1 means its counted, 0 means its disregarded).
So basically I need to count a day all tickets filed that day have a severity level of 1, 2, or 3, and the Ticket Flag is 1.
Hi @GuillaumeB,
Based on my test, you could refer to below formula:
Measure = CALCULATE(COUNT(Table1[Ticket date]),FILTER('Table1','Table1'[Ticket security level]=1||'Table1'[Ticket security level]=2||'Table1'[Ticket security level]=3),FILTER('Table1','Table1'[Ticket flag]=1))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
That's roughly what I was trying to make work at first but even when filter for August 2018 for example, I'm getting 38k results.
Try DISTINCTCOUNT instead of COUNT in the provided pattern.
DISTINCTCOUNT with the solution proposed earlier worked! Thank you.
Glad it worked. Can you mark my response as a solution as well? Thanks.
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 |
---|---|
108 | |
97 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |