Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AgeOfEgos
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

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
HotChilli
Super User
Super User

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

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.

 

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.

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!!

 

 

Thank you, enjoy!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.