Count of each case number for unique list of values

[thesword1404]

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:

MRN	Case Number
NCP1234	CS1641870
NCP1234	CS1641870
NCP1234	CS1641870
NCP1234	CS1641870
NCP1234	CS1641870
NCP 3692	CS1630783
NCP 3692	CS1630783

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"

[thesword1404]

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

[Samarth_18 · 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 ) )

[thesword1404]

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]

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

Thanks,

Samarth

[thesword1404]

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

[Samarth_18]

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