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 th...
Need Help
Tips and Tricks
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