Distinct count on a table visual

[Elisa112]

Hello Experts

I am trying to resolve how to count distinct values in a table visual, for our appointments table I need to count the number of appointments missed for each customer.  I have tried to summarise the appointments table but I get errors or it does not bring through duplicate values.

My appointments data looks like this, this is table visual taken from the Meetings table. Filtering on AppointmentType and AppStatus

CustID     AppointmentType      AppStatus   AppID    AppDate

001            First                          DNA            002             1 June

001            First                          DNA            003             5 June

002            First                          DNA            004               5 June

002            First                          DNA            005              7 June

Here is what I am looking for

CustID     AppointmentType      AppStatus   AppID    AppDate         Meetings Missed

001            First                          DNA            002             1 June             2

001            First                          DNA            003             5 June             2

002            First                          DNA            004               5 June           1

I can create a measure to count the distinct occurences:

Intro_DNA2 = CALCULATE(DISTINCTCOUNT(Meetings[CustID]), FILTER('meetings', 'Meetings'[AppointmentType] ="First" && 'Meetings'[AppStatus] = "DNA"))

But I want to show the number of missed appointments in for each customer in a column, what is the best way to achieve this? Should I just create a calculated column on the main meetings table for missed appointments, if so what is the best approach.

All help greatly appreciated,

[Anonymous · Accepted solution]

Hi, To count distinct missed appointments per customer and display it in a table, follow these steps

Step 1. Create a Calculated Column-- Add a column to your table to count the number of missed appointments per customer.

MissedAppointmentsCount = CALCULATE(COUNTROWS(Meetings), FILTER(Meetings, Meetings[CustID] = EARLIER(Meetings[CustID]) && Meetings[AppointmentType] = "First" && Meetings[AppStatus] = "DNA"))

Step 2. Add the Column to the Table Visual-- Include the new 'MissedAppointmentsCount' column in your table visual along with the other relevant columns.

This approach ensures you can see the number of missed appointments for each customer directly in your visual.

Please let me know if your question is answered.

Regards,
Chiranjeevi kudupudi

[Elisa112]

Thanks so much

[lkalawski]

@Elisa112 ,

To help you, I need some information. On what basis do you calculate missed appointments? Why should it be shown twice for CUST 1 and only once for CUST 02, what is the logic behind this?

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin

[Elisa112]

Hello

Apologise  I made a mistake, the correct version is below

CustID     AppointmentType      AppStatus   AppID    AppDate         Meetings Missed

001            First                          DNA            002             1 June             2

002            First                          DNA            004             5 June             2

I need to find the number of times a customer has missed the first appointment,  (DNA = did not attend).

Thanks for your assistance.

[Anonymous]

Create a measure as below. It might works for your requirements.

MissedFirstAppointments =
CALCULATE(
COUNTROWS(Meetings),
Meetings[AppointmentType] = "First" && Meetings[AppStatus] = "DNA"
)