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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Mahesh0016
Solution Sage
Solution Sage

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.


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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.