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

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

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)

3 REPLIES 3
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.

Community Champion

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)

Super User

@warnera , you need to use

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

## Helpful resources

Announcements

#### 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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors