cancel
Showing results for
Did you mean:
Helper II

## 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

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors