How to count unique Values in a duplicate rows

[biswad]

Hi Friends, 

I'm working on Power BI report and I have a table which contains list of Organisations and the roles within each of the organisation as below. I would like to count the "Total_Role" under each "ID". How can I achieve this, any help would be greatly appriciated. I'm quite new in dex.

Organistation	ID	Total_Role
Ab	1223	2
Ab	1223	2
Ab	1223	2
Bc	1332	1
Bc	1332	1

I'm expecting the measure should count only 1 role against each ID. So the total should be 3 not 8

Result	TotalRole
Ab	2
Bc	1

[RSC]

Hi @biswad ,

The Simplest way of doing that is, use SUMMARISE function

TotalRole = 
SUMX(
    SUMMARIZE('Table', 'Table'[Org], 'Table'[ID]),
    1
)

[DimaMD]

Hi @biswad  try it

= SUMX( SUMMARIZE('table','table'[Organistation],'table'[Total_Role]),[Total_Role])

[biswad]

Hi, thank you for your solution. But I'm looking to get the sum of the roles based on the ID , and when I use the query you have shared over the ID column it doesn't quite give me the result I was looking at. 

[DimaMD · Accepted solution]

@biswad  add ID to measure 

= SUMX( SUMMARIZE('table','table'[Organistation],'table'[ID],'table'[Total_Role]),[Total_Role])

[biswad]

Thank you, this works, I think I have an issue with my data. but I got an idea how to achieve it through your helpful query.

[biswad]

Thank you, this works, I think I have an issue with my data. but I got an idea how to achieve it through your helpful query.