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

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

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

        Hi Carliniiiii 

         

        You can use this measure 

        UniqueCategoryACount =
        CALCULATE(
            DISTINCTCOUNT('Table'[Identifier ]),
            'Table'[Category] = "A"
        )

        let me know if doesnot works

        Regards