Filtering table of distinct counts

[Jon54]

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

[Jon54 · Accepted solution]

@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] )

[v-jingzhang]

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]

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 · Accepted solution]

@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 · Accepted solution]

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