cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors