PowerBi: iteratively count distinct values in a column if two distinct values appear in another column

Question

In PowerBi I have a table with these columns:

+-----------+---------+---------------------+
| studentId | topicId | progressTrackerName |
+-----------+---------+---------------------+
|         1 |     100 | Black               |
|         2 |     100 | Black               |
|         2 |     101 | Green               |
|         2 |     102 | Black               |
|         2 |     103 | Green               |
|         2 |     104 | Black               |
|         3 |     100 | Black               |
|         4 |     100 | Black               |
|         4 |     101 | Green               |
+-----------+---------+---------------------+

I need to create a measure that iterates by studentId and counts the unique studentIds that have both Black and Green in the [progressTrackerName]. For example with the exemplary table above the measure should give 2 as a result (as studentIds 2 and 4 have both Black and Green, doesn't matter if more than once).

At the moment I have this but this only gives me the count of unique rows by [studentId], [topicId] and [progressTrackerName].

Black&Green =
COUNTROWS (
    GROUPBY (
        'Progress Tracker',
        'Progress Tracker'[studentId],
        'Progress Tracker'[topicId],
        'Progress Tracker'[progressTrackerName]
    )
)

It should instead also filter by [progressTrackerName] when the distinct values are >=2 for each studentId.

Any ideas? I am pretty sure I should use SUMX but I am quite new to PowerBi so can't quite figure out how. Thanks!

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-01T14:57:54

There are a bunch of ways to do this.

Using SUMMARIZE (similar to GROUPBY):

Black&Green =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Progress Tracker',
            'Progress Tracker'[studentId],
            "NameCount", DISTINCTCOUNT ( 'Progress Tracker'[progressTrackerName] )
        ),
        [NameCount] = 2
    )
)

You could also SUMX over the studentId values, adding 1 each time the distinct count is 2.

Black&Green =
SUMX (
    VALUES ( 'Progress Tracker'[studentId] ),
    IF (
        CALCULATE ( DISTINCTCOUNT ( 'Progress Tracker'[progressTrackerName] ) ) = 2,
        1
    )
)

In both of these examples, you're grouping and iterating over studentId and checking the distinct count of the color corresponding to each.

PowerBi: iteratively count distinct values in a column if two distinct values appear in another column

1 Answers