topic Patient Cohort (AND Slicer) in Quick Measures Gallery

Patient Cohort (AND Slicer)

Greg_Deckler — Mon, 10 Sep 2018 13:38:18 GMT

In the healthcare field, it is often desireable to identify a cohort of patients with similar, multiple diagnoses. This quick measure returns a comma-delimited list of patients that have all been identified with the same diagnoses. The tricky part here is that this allows the user to select from a slicer the diagnoses for which the user is interested in obtaining a cohort. Identified patients have had diagnoses that meet all of the selected criteria. In other words, all patients have had diagnoses for all of the selected diagnoses in the slicer. Essentially creates an AND for the slicer as opposed to the normal OR.

Cohort = 
VAR tmpTable1 = GENERATE(VALUES(Diagnosis[Patient]),
                         EXCEPT(
                                VALUES(Diagnosis[Diagnosis]),
                                CALCULATETABLE(VALUES(Diagnosis[Diagnosis]))))
VAR tmpTable2 = SUMMARIZE(tmpTable1,Diagnosis[Patient])
VAR tmpTable3 = EXCEPT(VALUES(Diagnosis[Patient]),tmpTable2)
RETURN CONCATENATEX(tmpTable3,[Patient],",")

This quick measure would take two inputs, the column for the ID to return (Patient) and the column for the slicer selection (Diagnosis)

Also included is the trivial variation, Count of Cohort:

Count of Cohort = 
VAR tmpTable1 = GENERATE(VALUES(Diagnosis[Patient]),
                         EXCEPT(
                                VALUES(Diagnosis[Diagnosis]),
                                CALCULATETABLE(VALUES(Diagnosis[Diagnosis]))))
VAR tmpTable2 = SUMMARIZE(tmpTable1,Diagnosis[Patient])
VAR tmpTable3 = EXCEPT(VALUES(Diagnosis[Patient]),tmpTable2)
RETURN COUNTROWS(tmpTable3)

eyJrIjoiZWYwNzZlNzctOTc5NC00ZWU1LWI2OWMtYTZjYTI0MjIzMjEzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Patient Cohort (AND Slicer)

CNENFRNL — Sun, 19 Mar 2023 19:40:13 GMT

You may want to try a measure with better performance

Measure = VAR __cnt = COUNTROWS( ALLSELECTED( Diagnosis[Diagnosis] ) ) RETURN CONCATENATEX( FILTER( VALUES( Diagnosis[Patient] ), CALCULATE( DISTINCTCOUNT( Diagnosis[Diagnosis] ) ) = __cnt ), Diagnosis[Patient], "," )

Re: Patient Cohort (AND Slicer)

Greg_Deckler — Sun, 19 Mar 2023 21:19:41 GMT

CNENFRNL I'm missing something...

Re: Patient Cohort (AND Slicer)

lherbert501 — Wed, 14 Jun 2023 14:02:19 GMT

Greg_Deckler How could this be amended so that the patient was in a table format?

Re: Patient Cohort (AND Slicer)

Greg_Deckler — Thu, 15 Jun 2023 13:00:55 GMT

lherbert501 Just drop the CONCATENATEX and return tmpTable3 or use the CONCATENATEX with UNICHAR(10) & (UNICHAR(13) as the delimeter.

Re: Patient Cohort (AND Slicer)

KervBruce — Wed, 10 Apr 2024 09:31:03 GMT

Thank you for directing me to this Greg_Deckler , it's a very elegant solution.
Picking up on the question from lherbert501 , How can this be adapted to return a table?
I tried simply returning tmpTable3 as suggested but that didn't seem to work. I also tried using the same code from the measure in a Table definition but that returned a blank. table too. What am I doing wrong?

Re: Patient Cohort (AND Slicer)

Greg_Deckler — Wed, 10 Apr 2024 13:00:27 GMT

KervBruce So to adapt this to return a table, you would implement a Complex Selector. The Complex Selector - Microsoft Fabric Community. The exact solution is going to depend on whether your TagName slicer table is related to your main fact table or coming from your main fact table. Is it or is it a disconnected table?

Re: Patient Cohort (AND Slicer)

AnilKumar2vsu — Fri, 17 May 2024 08:49:07 GMT

How can we reflect this measure results to all report pages.

For example: we are having WA table in this Id number,option number, option discrimination are the columns.

Id number is unique (99 distinct ID's)

Option number having duplications(767 distinct option numbers)

In option number slicer we need to get AND condition,we will get the result by above example

Here I need to reflect the multiple option number belonging ID number to all the report pages

How can I achieve it

Re: Patient Cohort (AND Slicer)

magnusks — Fri, 17 Jan 2025 06:37:27 GMT

Hi Greg_Deckler,

Though this is an old post, I hope you'll be able to answer/help me with my problem.

In my data I have three different categories that I want to use as slicers, and I want to be able to show the projects that "checks off" for all selected categories. I.e, if I select "Environment" and "Red", only project 7 and 10 should show up (not 1,4,5, 8 and 9 - they tick off for one of the two and not both).

I am able to reproduce results similar to yours using my data (sheet; combined in the pbix-file). However, I can only figure out how to do it if I combine all categories into one coloumn (Table - category combined[Value]) and further, 1 slicer. What I want, however, is to have seperate slicers for each category and still only see the projects that "ticks off" for all selected categories (Table, sheet; "Not combined" in PBIX-file).

I hope this makes sense - If not, please do not hesitate to ask me to clarify.

Cheers,

MKS