Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Distinct Count with text column condition

Hi,
How can I count the Distinct number of cases where the count of "Yes"is > 1

Case noYes Column
12 
12 
12Yes
13 
13Yes
13Yes

 

 

 

Count of Cases = CALCULATE(DISTINCTCOUNT('Table'[Case no]),FILTER('Table','Table'[Yes Col] = "Yes"))​

 

 

 

I can calculate the number of cases but i don't want the cases with only one "Yes". So from the above table it should skip case no 12 and should give answer as 1 i.e caseno 13

Thanks!!

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1670300720127.png

 

 

expected result measure: =
COUNTROWS (
    FILTER (
        DISTINCT ( 'Table'[Case no] ),
        CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Yes Column] = "Yes" ) ) ) > 1
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Mahesh0016
Super User
Super User

Mahesh0016_0-1670300765473.pngMahesh0016_1-1670300789372.png

Hello,
Good monrning,
i think above two solutions in one of the solutions useful for you.

Anonymous
Not applicable

Thankyou for replying.

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1670300720127.png

 

 

expected result measure: =
COUNTROWS (
    FILTER (
        DISTINCT ( 'Table'[Case no] ),
        CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Yes Column] = "Yes" ) ) ) > 1
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Anonymous
Not applicable

Hi,

Thankyou. This measure works the best.

Regards,

Shubham

amitchandak
Super User
Super User

@Anonymous , have these two measures

 

measure 1= calculate(count('Table'[case no]),FILTER('Table','Table'[Yes Col] = "Yes"))

 

 

Measure
count GT 1 = countx(values('Table'[case no]), if([measure 1]>1, [case no], blank()))

Anonymous
Not applicable

Thankyou for replying.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors