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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
warnera
Helper I
Helper I

Distinct total based on another column

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

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @warnera ,

 

Apart from Solution mentioned abovem you can try

 

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

 

Regards,
Harsh Nathani

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

View solution in original post

3 REPLIES 3
rubinboer
Resolver II
Resolver II

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
Community Champion
Community Champion

Hi @warnera ,

 

Apart from Solution mentioned abovem you can try

 

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

 

Regards,
Harsh Nathani

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

amitchandak
Super User
Super User

@warnera , you need to use

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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