Counting days without Level 4 tickets

[GuillaumeB]

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.

[v-danhe-msft · Accepted 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

[CheenuSing]

Hi @GuillaumeB

Please share data / data model / pbix on google drive /onedrive and paste the link here.

Cheers

CheenuSing

[GuillaumeB]

@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. 

[v-danhe-msft · Accepted 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

[GuillaumeB]

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.

[dedelman_clng · Accepted solution]

Try DISTINCTCOUNT instead of COUNT in the provided pattern.

[GuillaumeB]

DISTINCTCOUNT with the solution proposed earlier worked! Thank you.

[dedelman_clng]

Glad it worked. Can you mark my response as a solution as well? Thanks.