Distinct total based on another column

[warnera]

Hello, I have a data set like this. I am trying to get a distinct count of the medication per MRN. The medication may repeat for the MRN but I would only like to count it once.

MRN    Medication

0001    Drug A

0001    Drug A

0001    Drug B

0002    Drug C

0002    Drug A

0003    Drug B

0003    Drug B

I need the data to look like this

Drug A - 2

Drug B - 2

Drug C -1

[rubinboer]

a normal table can visualize the data as it is, should you not need the count in other calculations. Add a table to the canvas drop the Medicine column in it first and then add th distinct count of the MRN. rename the Count of MRN to MRN. a quick ways if all you want is the visualization.

[harshnathani · Accepted solution]

Hi @warnera ,

Apart from Solution mentioned abovem you can try

Count = CountROWS(SUMMARIZE('Table','Table'[Medication],'Table'[MRN ]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@warnera , you need to use

distinctcount(Table[MRN]) and view it by Medication