Forum Discussion

Jon54's avatar
Jon54
Frequent Visitor
3 years ago
Solved

Filtering table of distinct counts

Hello,

I have a dataset with patient visits to a clinic by date. I have been able to create a measure that counts how many days each patient visits the clinic in a year and totals that number for all patients:

Distinct visits = var _table=SUMMARIZE('Backing data','Backing data'[Attendance Date],"distinctcount",DISTINCTCOUNT('Backing data'[Patient]))
return
SUMX(_table,[distinctcount])

 

I want to be able to sum distinct visits where patients visit the clinic multiple times (2 or more) so ignoring single visits. How might I change my DAX to do that? 

Thanks

Need Help
  • Jon54's avatar
    Jon54
    3 years ago

    v-jingzhang thanks, appreciate you coming back to me. It is the syntax I am struggling with. What I really need is a count of the patients who have multiple visits. If I make the following change it deson't work. Why would that be?

    Distinct visits2 = VAR _table =
    SUMMARIZE (
        'Backing data',
        'Backing data'[Patient],
        "countOfVisitDates", DISTINCTCOUNT ( 'Backing data'[Attendance Date] )
    )
RETURN
    SUMX ( FILTER ( _table, [countOfVisitDates] > 1 ), [Patient] )
  • Jon54's avatar
    Jon54
    3 years ago

    v-jingzhang Hello, I changed SUMX to COUNTX. I now get the number I want. Thanks. This has been driving me mad

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Jon54 

     

    The measure you shared is not for counting how many days each patient visits the clinic in a year. It is summarizing on [Attendance Date] and counting how many distinct patients visit the clinic on each date and the total of that for all dates. In addition, when you say patients visit the clinic multiple times, do you refer to the patients that visit the clinic multiple times in a year? 

     

    Best Regards,
    Community Support Team _ Jing

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi Jon54 

       

      Update: if I understand it correctly, you can try this measure:

      Distinct visits =
VAR _table =
    SUMMARIZE (
        'Backing data',
        'Backing data'[Patient],
        "countOfVisitDates", DISTINCTCOUNT ( 'Backing data'[Attendance Date] )
    )
RETURN
    SUMX ( FILTER ( _table, [countOfVisitDates] > 1 ), [countOfVisitDates] )

       

      Best Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as Solution to help other members find it.

      • Jon54's avatar
        Jon54
        Frequent Visitor
        3 years ago

        v-jingzhang thanks, appreciate you coming back to me. It is the syntax I am struggling with. What I really need is a count of the patients who have multiple visits. If I make the following change it deson't work. Why would that be?

        Distinct visits2 = VAR _table =
    SUMMARIZE (
        'Backing data',
        'Backing data'[Patient],
        "countOfVisitDates", DISTINCTCOUNT ( 'Backing data'[Attendance Date] )
    )
RETURN
    SUMX ( FILTER ( _table, [countOfVisitDates] > 1 ), [Patient] )