cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Count visits by Patient--then show all patients with a count >=2

I have a SQL statement pulling all visits for patients.  I would like to count those visits by patient name and then show a list of names where they have 2 or more visits.  I would like to make this a measure and not use a filter for it in a visual.  I've tried the following code;

 

PatientsVisitsGreaterThan2 = CALCULATE(
                                DISTINCTCOUNT(Query1[Patient Name]),
                                FILTER('Query1',CALCULATE(COUNT('Query1'[Service Date]), ALL('Query1'[Service Date]))>=2))

This returns 4 patients--when it should return 1017.  When I look at the 4 patients it's returning--they are patients that had 2 or more visits on the SAME service date.  I don't care if it's the same service date or not--I just want to see if they had 2 or more visits (I do have a date slicer--but other than that user input--I don't care).


Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: Count visits by Patient--then show all patients with a count >=2

The problem here is that the FILTER part of your calculation works on the query1 table, meaning that for each row in query1 (each individual visit I presume) the requirement of at least two visits is applied. The logic is a bit twisted here, but I can somewhat understand that the result is a list of visits for which there's at least one other visit on the same day. The result of FILTER is then applied as a filter for counting the patients.

 

What should happen, of course, is not counting the visits, but the patients. So starting with the FILTER:

FILTER(VALUES(Query1[Patient Name]), CALCULATE(COUNTROWS(Query1))>= 2)

 

VALUES returns a list of unique values of the [Patient Name] column. We only want those values for which the number of rows corresponding to that value is at least 2.

Now, you could use the FILTER result as a filter in CALCULATE as you tried before, but it is already the list that you're looking for. So the only thing left is to count how many rows this list has:

COUNTROWS( FILTER(VALUES(Query1[Patient Name]), CALCULATE(COUNTROWS(Query1))>= 2) )

 

Obviously, this doesn't take into account that different patients may share the same name. So you should really work with patient number or another unique identifier.

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Count visits by Patient--then show all patients with a count >=2

What does the data look like?  If we assume it's    name, date   on each row then an explicit measure to count patient rather than date should work, 

MeasureCount = COUNT(table[Patient])

can be created first.

Then pass this into another measure similar to the one you created and use it to filter

Measure = CALCULATE(
                                DISTINCTCOUNT(table[Patient]),
                                FILTER('table', [MeasureCount] > 2))

I hope it helps

Highlighted
Helper I
Helper I

Re: Count visits by Patient--then show all patients with a count >=2

Yes, it's organized;

 

Name---ServiceDate

 

So if a patient was seen multiple times;

 

Test Patient-----1/1/19

Test Patient-----1/4/19

Test Patient 2-----1/1/19

Test Patient 2----1/8/19

etc.

 

The first measure works out and shows an accurate number of total visits.  The second measure;

PatientsVisitsGreaterThan2 = CALCULATE(
                                DISTINCTCOUNT(Query1[Patient Name]),
                                FILTER('Query1', [MeasureCount] >= 2))

Returns 4 as a total number of patients.  When I investigate that data--it shows 4 patients that were seen twice on the same day.  Not a total number of patients that were seen 2 or more times over every date.

 

Highlighted
Resolver III
Resolver III

Re: Count visits by Patient--then show all patients with a count >=2

The problem here is that the FILTER part of your calculation works on the query1 table, meaning that for each row in query1 (each individual visit I presume) the requirement of at least two visits is applied. The logic is a bit twisted here, but I can somewhat understand that the result is a list of visits for which there's at least one other visit on the same day. The result of FILTER is then applied as a filter for counting the patients.

 

What should happen, of course, is not counting the visits, but the patients. So starting with the FILTER:

FILTER(VALUES(Query1[Patient Name]), CALCULATE(COUNTROWS(Query1))>= 2)

 

VALUES returns a list of unique values of the [Patient Name] column. We only want those values for which the number of rows corresponding to that value is at least 2.

Now, you could use the FILTER result as a filter in CALCULATE as you tried before, but it is already the list that you're looking for. So the only thing left is to count how many rows this list has:

COUNTROWS( FILTER(VALUES(Query1[Patient Name]), CALCULATE(COUNTROWS(Query1))>= 2) )

 

Obviously, this doesn't take into account that different patients may share the same name. So you should really work with patient number or another unique identifier.

View solution in original post

Highlighted
Helper I
Helper I

Re: Count visits by Patient--then show all patients with a count >=2

RE:  Yeah I'm pulling in person_id too, just haven't updated my SQL yet.

 

Nailed it and what a great explanation.  Have a great day and thank you!!

 

 

Highlighted
Resolver III
Resolver III

Re: Count visits by Patient--then show all patients with a count >=2

Thank you, enjoy!

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors