Forum Discussion

Elisa112's avatar
Elisa112
Helper V
2 years ago
Solved

Distinct count on a table visual

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,

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

5 Replies

  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    2 years ago

    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's avatar
      Elisa112
      Helper V
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

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


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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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