Search code examples
group-bypowerbisummaxdax

DAX Measure to calculate MAX by Group and SUM the results but a Super Group

I have the following table below

enter image description here

Name is the person in question and we have several activities listed per person. Compliant is whether that activity was compliant or not, it can only be 1 or 0. Category is the grouping of the people.

Given this information, I need to calculate the following table below in Power BI.

enter image description here

I need to provide a count of how many people had at least one compliant activity. Ideally this should be flexible enough that I can provide a total number how many people have at least one compliant activity. I have category as an example here, but there are slicers that I will need to use here to further drill down the data.

Solution

  • enter image description here

    Drag category into a table visual and then also add the following measure.

    Measure = CALCULATE( COUNTX(VALUES('Table'[Name]), 'Table'[Name]), 'Table'[Compliant] = 1)