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

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