Total Number of Tickets with One Service Dispatch

[Anonymous]

Hello,

We are building a summary visual that returns "Total # of Tickets", "Total # of Dispatches" and "Total # of Tickets with 1 Dispatch", based on ticket information (please see below).

The issue that we are running into is that the "Total # of Tickets with 1 Dispatech" field is returning the incorrect value. In this case it should return 1, instead of 7. 

I am using the following calculated columns:

Number of Dispatches = CALCULATE(COUNT(SV_Service_Ticket_Dispatch[Schedule_Time].[Date]), SV_Service_Ticket,SV_Service_Ticket[Ticket_Number])

Total # of Tickets with 1 Dispatch = COUNTROWS(FILTER(SV_Service_Ticket_Dispatch,SV_Service_Ticket_Dispatch[Number of Dispatches] = 1))

I know that the "Total # of Tickets with 1 Dispatch" formual is wrong, but don't know how to go about writing the proper formula for my desired result. 

Any help will be greatly appreciated.

[Anonymous]

Hello @jdbuchanan71 ,

Would you be able to take a look at what I am trying to accomplish and tell me where am I going wrong?

Thank you,

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

When you are working in a model you should avoid implicit measures.  What do I mean?  In your visual the [Total # of Tickets] is just the SV_Service_Ticket[Ticket_Number] field pulled into the table and set to count.  You are better off making a measure for it so we can use it in late measures.  So we end up with these 3:

Ticket Count = COUNTROWS ( SV_Service_Ticket )

Dispatches Count = COUNTROWS ( SV_Service_Ticket_Dispatch )

Tickets with 1 dispatch = 
CALCULATE(
    [Ticket Count],
    FILTER ( SV_Service_Ticket, [Dispatches Count] = 1 )
)

Is that what you were looking for?

[Anonymous]

Hello @jdbuchanan71 ,

Your solution worked like a charm. Also, thank you for the recommendation about avoiding implicit measures. I am a casual user of the tool and appreciate your time and help.

[Nathaniel_C]

Hi @Anonymous 

Try this:

CALCULATE(COUNTROWS(SV_Service_Ticket_Dispatch),SV_Service_Ticket_Dispatch[Number of Dispatches] = 1)

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hello @Nathaniel_C ,

Thank you for your help with this matter. I tried your proposed solution, but it did not work. Attached please find the PBIX file I am using, for your review.

 https://www.dropbox.com/s/7iecu9lqv06dtzn/Total%20Number%20of%20Tickets%20with%20One%20Dispatch%20-%...