Forum Discussion

biswad's avatar
biswad
Icon for Advocate I rankAdvocate I
2 years ago
Solved

How to count unique Values in a duplicate rows

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.

 

OrganistationIDTotal_Role
Ab12232
Ab12232
Ab12232
Bc13321
Bc13321

 

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

ResultTotalRole
Ab2
Bc1
powerbi
  • DimaMD's avatar
    DimaMD
    2 years ago

    biswad  add ID to measure 

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

     

6 Replies

  • DimaMD's avatar
    DimaMD
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi biswad  try it

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

     

    • biswad's avatar
      biswad
      Icon for Advocate I rankAdvocate I
      2 years ago

      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's avatar
        DimaMD
        Icon for Solution Sage rankSolution Sage
        2 years ago

        biswad  add ID to measure 

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

         

  • RSC's avatar
    RSC
    Icon for Helper I rankHelper I
    2 years ago

    Hi biswad ,

    The Simplest way of doing that is, use SUMMARISE function

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