Formula for more than 1 patient

[jonnyA]

A client asked if I could create a report that showed the # of duplicate patients.  I'm not sure how to create that formula.  Any help is appreciated.

My dataset is Demo dataset.  And I assume I will have to use the Data Field Patient Name.

Thank you.

[chonchar · Accepted solution]

Hi, I'm new here. I'll take a shot at this. 

If you just want the number, you can create a measure that counts rows (COUNTROWS) and another that counts distinct entries (DISTINCTCOUNT) in those rows. Then another measure that is (COUNTROWS) - (DISTINCTCOUNT) and that will populate the answer of dublicates. 

[Anonymous · Accepted solution]

Hi @jonnyA ,

@chonchar 's reply is a good idea. And you said you want a report showed them. You can create a measure as the visual-level filter for filtering.

Below is the sample for reference.

Based on your requirement, you need to show only patient A and patient B in the table visual.

The solution is to create a measure.

Measure = var _dis=CALCULATE(DISTINCTCOUNT('Table'[patient]),FILTER(ALLSELECTED('Table'),[patient]=MAX('Table'[patient])))
var _cou=CALCULATE(COUNT('Table'[patient]),FILTER(ALLSELECTED('Table'),[patient]=MAX('Table'[patient])))
return IF(_cou<>_dis,1)

Put the measure into the visual-level filters and set up show items when the value is 1. Below is the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[chonchar · Accepted solution]

Hi, I'm new here. I'll take a shot at this. 

If you just want the number, you can create a measure that counts rows (COUNTROWS) and another that counts distinct entries (DISTINCTCOUNT) in those rows. Then another measure that is (COUNTROWS) - (DISTINCTCOUNT) and that will populate the answer of dublicates.