Power BI DAX Measure: Counting the distinct number of users by nested way of filtering

Question

I tried multiple ways to do this, but just can't find the final solution. Precodition: it has to be a MEASURE, NOT calculated column

So my table looks like this:

Name	Skill	Level
John	SQL	2
John	SQL	2
Amy	Python	1
Amy	Python	3
Lena	CSharp	2
Lena	SQL	2

The solution should be: count of people who have same level for the same skill. So in this example, the answer is 1 because only John has same skill and the same level.

It should be something like this:

count number of people if, for the same skill, they have the same level number

I don't know how to even search for the same skill in the same column.

EARLIER is not working for measure, and MAX is giving totally wrong answer.

Answer 1

Try:

Same Skill Same Level = 
  COUNTROWS(
    FILTER(
      GROUPBY(
        'Table',
        'Table'[Name],
        'Table'[Skill],
        'Table'[Level],
        "Count", COUNTX(CURRENTGROUP(), 1)
      ),
      [Count] > 1
    )
  )

This will give you number of people and number of same skill & level. So if John had another skill with the same level, then it would show 2.

If you want distinct count of folks with one or more pairs then use:

Same Skill Same Level - people = 
  CALCULATE(
    DISTINCTCOUNT('Table'[Name]),
    FILTER(
      GROUPBY(
        'Table',
        'Table'[Name],
        'Table'[Skill],
        'Table'[Level],
        "Count", COUNTX(CURRENTGROUP(), 1)
      ),
      [Count] > 1
    )
  )