Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dataset filtering within subgroup

Hi, I'm trying to write a DAX formula to ascertain proportion of smokers within each cancer group of patients.

As patients can have more than 1 cancer, I've created a separate table and joined to the master table as 'many to 1' relationship.

The table in image below is incorrectly showing total smokers across each cancer type. I need it to show number of smokers within each cancer type for patients identified in DiagnosedWithCancer flag (field contains 1s and 0s).

Can you help?

I have the pbix but unable to upload, only photos and video in the menu bar. If it is possible to upload pbix please let me know as it's easier to adapt to the file.

 

Many thanks in advance

 

 

 

 

Need Help
Tips and Tricks

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Greg,

      here are samples from both tables, joined many to 1 from left to right using patient_ID.

       

      I want to calculate patients who smokes (smoker = 1) that has been diagnosed with cancer (DiagnosedWithCancer = 1) grouped by Health Condition. In the screenshot patient_ID 1000006 would fit under this criteria. The earlier screenshot shows total 40 smokers across all cancer types which is incorrect, I need the number of smokers broken down by cancer type.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        Is the [smoker] column a flag to mark the smoking patient? If so you could create a calculated column by using lookupvalue() function to add the [smoker] column to the first table. Then create a masure as below.

        sum of smoker = calculate(distinctcount([patient id]),filter('healthcondition','healthcondition'[smoker] = 1))

         

        Best Regards,

        Jay