Creating a Grouping Within IDs with DAX

[Darko_Giac]

Hi all,

I'm working with a fairly large survey data set and am just struggling slightly with finding a way to categorize respondents into certain groups depending on their response choice.

The response options include ADHD, OCD, VisualImpairment, MotorImpairment, Dysgraphia, and Dyslexia. 

The respondents might have duplicate rows, so I would like to categorize any respondent who chose either ADHD or OCD into Group 1, and respondents who chose anything else into Group 2 (using DAX)

I know I need to use the Filter function, but not quite sure how to nest it.

I've attached a workbook which will hopefully provide further clarity.

TestWorkbook

Any help/advice is greatly appreciated!

Currently using Office 365 ProPlus

Darko

[Ashish_Mathur]

Hi,

I am assuking that Column1 shows your expected result.  What does Group 1 & Group 2 mean? Why is Student 1 falling under Group 1 & Group 2?

[Zubair_Muhammad · Accepted solution]

@Darko_Giac

You can use a column like

Column =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[DxGrouping] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[DxGrouping] IN { "ADHD", "OCD" }
    ),
    "Group 1 & 2",
    "Group 2"
)