Forum Discussion
Filtering table of distinct counts
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 Hello, I changed SUMX to COUNTX. I now get the number I want. Thanks. This has been driving me mad
Community SupportHi 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.
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 Hello, I changed SUMX to COUNTX. I now get the number I want. Thanks. This has been driving me mad