cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors