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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
thesword1404
New Member

Count of each case number for unique list of values

hi everyone,

 

I would like someone to help me please as I have a list of MRN and each MRN has many case numbers.

the task is to calculate how many MRN have more than 5 case numbers:

MRNCase Number
NCP1234CS1641870
NCP1234CS1641870
NCP1234CS1641870
NCP1234CS1641870
NCP1234CS1641870
NCP 3692CS1630783
NCP 3692CS1630783

 

I tried with the below code:

 

mesure=
calculate( COUNTROWS(optimize),
FILTER(
VALUES(optimize[MRN]),
COUNTROWS(
FILTER(optimize, optimize[Case Number] )
)>5
)
)

but this message appears : 

"can't convert values of type text"

1 ACCEPTED SOLUTION

@thesword1404 , Hope this will give you desired output. Use it directly on card visual:-

 

_count_case =
VAR _Table =
    SUMMARIZE (
        'optimize',
        'optimize'[MRN],
        'optimize'[Case Number],
        "Count", COUNTROWS ( 'optimize' )
    )
RETURN
    COUNTROWS ( FILTER ( _Table, [Count] >= 5 ) )

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

6 REPLIES 6
thesword1404
New Member

thanks for your effort but this is not what I am looking for, I need a measure to compute the count of MRNs that have 5 or more case numbers without using filters from outside

@thesword1404 , Hope this will give you desired output. Use it directly on card visual:-

 

_count_case =
VAR _Table =
    SUMMARIZE (
        'optimize',
        'optimize'[MRN],
        'optimize'[Case Number],
        "Count", COUNTROWS ( 'optimize' )
    )
RETURN
    COUNTROWS ( FILTER ( _Table, [Count] >= 5 ) )

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

thank you soooo much dear for your kind help.

I actulay modfied the code a littele bit but the you gave me what I needed exactly:

 

count of case numbers = 
VAR casees = SUMMARIZECOLUMNS ( Table1[MRN], 
"count", COUNTROWS ( Table1 ) )
 RETURN 
COUNTROWS ( FILTER ( casees, [count] > 5 ) )
Samarth_18
Community Champion
Community Champion

Hi @thesword1404 ,

 

Create a measure like below:-

_count =
COUNTROWS (
    FILTER ( 'Table (3)', 'Table (3)'[MRN] = MAX ( 'Table (3)'[MRN] ) )
)

 

and use it on visual as filter like below:-

image.png

 

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

thanks for your reply but what I need acutely is a card visual that shows the number of counts for each MRN that have more than 5 case numbers

@thesword1404 In that case you need multicard visual and add measure like below:-

image.png

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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