Patient Cohort (AND Slicer)

Question

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.
&nbsp;
&nbsp;
Cohort = VAR tmpTable1 = GENERATE(VALUES(Diagnosis[Patient]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXCEPT(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES(Diagnosis[Diagnosis]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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)
&nbsp;
&nbsp;
Also included is the trivial variation, Count of Cohort:
&nbsp;
&nbsp;
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)
&nbsp;
&nbsp;
&nbsp;
eyJrIjoiZWYwNzZlNzctOTc5NC00ZWU1LWI2OWMtYTZjYTI0MjIzMjEzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

cnenfrnl · Answer

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],
        ","
    )

greg_deckler · Answer

CNENFRNL&nbsp;I'm missing something...

&nbsp;

lherbert501 · Answer

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

greg_deckler · Answer

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

kervbruce · Answer

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