Forum Discussion
Helper ICounting days without Level 4 tickets
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.
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.
7 Replies
Hi GuillaumeB
Please share data / data model / pbix on google drive /onedrive and paste the link here.
Cheers
CheenuSing
GuillaumeBAnonymous 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.
v-danhe-msftMicrosoft Employee
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
