cancel
Showing results for
Did you mean:
Helper I

## Distinct Count on "active" status rows where there's no similar rows with "completed"

Hi All

I have a table like this...

 user course status U5676 C2910 active U5676 C2910 completed U5676 C2938 active U5676 C2930 active U5676 C2985 active U5676 C2915 active

How can I create a measure to calculate a distinct count on users in "active" courses, excluding rows where a user also has a row where the course has been marked as "completed"? I'm tring to exclude both rows in the count but just can't figure it out.

Expected result would be a distinct count of 4...

 user course status U5676 C2938 active U5676 C2930 active U5676 C2985 active U5676 C2915 active

Many thanks

onedayover

1 ACCEPTED SOLUTION
Memorable Member

Hi

I did create the below measure and put it in the Filter for the particular visual :

Status Check measure =
Var earlierStatus = calculate( max(Sheet77[status]), all(Sheet77), Sheet77[course] = SELECTEDVALUE(Sheet77[course]))
Var CurrStatus = SELECTEDVALUE(Sheet77[status])
return if (CurrStatus = earlierStatus && CurrStatus = "active", 1, 0)

Appreciate your Kudos and please mark it as a solution if it helps you

Mahyartf
Memorable Member

Hi

I did create the below measure and put it in the Filter for the particular visual :

Status Check measure =
Var earlierStatus = calculate( max(Sheet77[status]), all(Sheet77), Sheet77[course] = SELECTEDVALUE(Sheet77[course]))
Var CurrStatus = SELECTEDVALUE(Sheet77[status])
return if (CurrStatus = earlierStatus && CurrStatus = "active", 1, 0)

Appreciate your Kudos and please mark it as a solution if it helps you

Mahyartf

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors