Forum Discussion

Carliniiiii's avatar
Carliniiiii
Frequent Visitor
2 years ago
Solved

Count based on distinct other column

Hello Power BI community,

 

I want to count the categories in a table per distinct identifier once. I can't remove the duplicates as they contain different data in other columns. 

 

Identifier Category
Title 1A
Title 1A
Title 1A
Title 2B
Title 2B
Title 3A
Title 3A
Title 4C

 

Desired Output 

A2
B1
C1

Ideally I can utilize the output directly in a pie chart to show the distribution of the categories whether be it in a measure or a separate column. 

 

Thank you all for your help. 

  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    2 years ago

    Hi   Carliniiiii 

    If my understanding is correct you want distinct count of identifier for a given category?

     

    it can be direclty also in power in pie chart check below image

    is identifier in value and take distinct count or you can create measure:

    UniqueCategoryCount =
    CALCULATE(
        DISTINCTCOUNT('Table'[Identifier ]),
        ALLEXCEPT('Table', 'Table'[Category])
    )

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
    Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

    Regards

5 Replies

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

    Hi   Carliniiiii 

    If my understanding is correct you want distinct count of identifier for a given category?

     

    it can be direclty also in power in pie chart check below image

    is identifier in value and take distinct count or you can create measure:

    UniqueCategoryCount =
    CALCULATE(
        DISTINCTCOUNT('Table'[Identifier ]),
        ALLEXCEPT('Table', 'Table'[Category])
    )

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
    Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

    Regards

  • Carliniiiii's avatar
    Carliniiiii
    Frequent Visitor
    2 years ago

    Hi qqqqqwwwweeerrr,

    thank you for your help. Is there also a way to extract the category amount and represent it in a card? So for instance Title category = A, Amount 2 

     

    Thanks

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

      Hi Carliniiiii 

       

      If that answer helps you please mark that as an accpeted solution so other might use if they have similar problem. 

       

      Seocnd: when you amount? is the same as count?or what like if category is B then what is expectation other than this do you want to only show A or B reason being Card will only take first value or total value or spefic manually given say only want to show value for A. I might have confused you but can please elobrate your question in detail with expected output and if any condition also want to included.

      Regards

      • Carliniiiii's avatar
        Carliniiiii
        Frequent Visitor
        2 years ago

        Hi qqqqqwwwweeerrr 

         

        "spefic manually given say only want to show value for A" 

         

        I think this is the most accurate answer. Basically I want to create a card which represents the counted amount of category A based on the measure above. So the card says as callout value "2"

         

        Thanks