skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Office 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Retail
    • For analysts
    • For IT
    • For developers
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
      • Overview
      • Guided learning
      • Documentation
      • Webinars
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign In
    • ·
    • Help
    Go To
    • Galleries
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • MBAS Gallery
    • MBAS Gallery 2020
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Patient Cohort (AND Slicer)

    Patient Cohort (AND Slicer)

    04-08-2018 12:54 PM - last edited 09-10-2018 06:38 AM

    Super User IV Greg_Deckler
    Super User IV
    2002 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User IV Greg_Deckler
    Super User IV
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Patient Cohort (AND Slicer)

    ‎04-08-2018 12:54 PM

    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


    ---------------------------------------

    @ me in replies or I'll lose your thread!!!

    I have a NEW book! 
    DAX Cookbook from Packt
    Over 120 DAX Recipes!




    Did I answer your question? Mark my post as a solution!

    Proud to be a Super User!




    Cohort.pbix
    104 KB
    Labels:
    • Labels:
    • Filters
    • Other
    Message 1 of 1
    2,002 Views
    4
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    • © 2021 Microsoft
    • Follow Power BI
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks