cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vjnvinod
Super User
Super User

How to remove duplicate from measure

hi Team,

 

I am using a function countrows to get the count details of the regions, however i  don't want the duplicate to be counted 2 or 3 times based on their repetition

for instance if Canada is repeating 3 times, it should only count as 1 

how do i remove the duplicate?

can someone help me with the measure?

 

Capture.PNG

1 ACCEPTED SOLUTION
nandic
Memorable Member
Memorable Member

@vjnvinod ,
Countrows function returns count of rows per criteria.
If there are 3 rows for Canada, it will return value 3.
On the other hand, if you would like to count have many distinct items(for example) were sold in Canada, they you should write measure like this:

DistinctCount = DISTINCTCOUNT('Item'[Item Id])

Furthermore, if you are using list or matrix, it will give you unique combinations of data (unique combinations of country/region) so there should be no duplicate rows on visualization.

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @vjnvinod 

Based on your description, I created data to reproduc your scenario. The pbix file is attached in the end.

MSL:

c1.png

 

You may create a measure as below.

Count = 
COUNTROWS(
    DISTINCT(MSL[Region])
)

 

Result:

c2.png

 

Best Regards

Allan

 

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

 

 

 

Greg_Deckler
Super User
Super User

@vjnvinod - Use DISTINCTCOUNT or maybe COUNTROWS(DISTINCT(...))

 

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Pragati11
Super User
Super User

HI @vjnvinod ,

 

 

Use DISTINCTCOUNT to count unique rows for a column rather than using COUNTROWS or you can use DISTINCT with COUNTROWS.

 

UniqueCount = DISTINCTCOUNT(tablename[column])

 

OR

 

UniqueCount = COUNTROWS(DISTINCT(tablename[column]))

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

nandic
Memorable Member
Memorable Member

@vjnvinod ,
Countrows function returns count of rows per criteria.
If there are 3 rows for Canada, it will return value 3.
On the other hand, if you would like to count have many distinct items(for example) were sold in Canada, they you should write measure like this:

DistinctCount = DISTINCTCOUNT('Item'[Item Id])

Furthermore, if you are using list or matrix, it will give you unique combinations of data (unique combinations of country/region) so there should be no duplicate rows on visualization.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors