cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
onedayover
Helper I
Helper I

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

Hi All

I have a table like this...

usercoursestatus
U5676C2910active
U5676C2910completed
U5676C2938active
U5676C2930active
U5676C2985active
U5676C2915active


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

 

usercoursestatus
U5676C2938active
U5676C2930active
U5676C2985active
U5676C2915active

 

Many thanks

onedayover

1 ACCEPTED SOLUTION
MahyarTF
Memorable Member
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)
MahyarTF_0-1670293639357.png

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

Mahyartf

View solution in original post

1 REPLY 1
MahyarTF
Memorable Member
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)
MahyarTF_0-1670293639357.png

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

Mahyartf

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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