Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count based on column

Hi All,

 

I want to count the number of distinct records based on the category column.

This is the calculation I have done 

Measure =
CALCULATE(
DISTINCTCOUNT(Table1[Category]),
ALLEXCEPT(Table1, Table1[Group], Table1[Type])
)
But this is just giving me the calculation of all the distinct category values only

 

 

I would like this to displayed be in a card visual:

e.g. Select Category = 2

Premium Category = 2

Basic Category = 4

 

Group

Type

Category

ABC

GEN

Select

ABC

GEN

Premium

ABC

GEN

Premium

ABC

GEN

Basic

ABC

GEN

Basic

ABC

GEN

Basic

ABC

GEN

Basic

ABC

GEN

Select

 

 

Please can someone advise? Thanks in advance

PowerBI 

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , To me this seem count

     

    count(Table1[Category])

     

     

    There is not distinct value column I can see here, if there is one you can try like

    Measure =
    Sumx(Values(Table1[Category]), calculate(
    DISTINCTCOUNT(Table1[ID]))
    )

  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi Anonymous 

     

    As you mentioned you want to display all value in cards. Since you have multiple categories so you have to use multi-row card visual.

    Under that visual you can directly drag your category and take a count of it.

    PFA screenshot for reference:-

     

    Thanks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , To me this seem count

     

    count(Table1[Category])

     

     

    There is not distinct value column I can see here, if there is one you can try like

    Measure =
    Sumx(Values(Table1[Category]), calculate(
    DISTINCTCOUNT(Table1[ID]))
    )

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    5 years ago

    Hi Anonymous 

     

    As you mentioned you want to display all value in cards. Since you have multiple categories so you have to use multi-row card visual.

    Under that visual you can directly drag your category and take a count of it.

    PFA screenshot for reference:-

     

    Thanks

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    your ALLEXCEPT doesn't make sense.

    Try it without and it should work:

    Measure = 
CALCULATE(
    DISTINCTCOUNT(Table1[Category])
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis