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
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