Forum Discussion

misul's avatar
misul
Helper I
8 years ago
Solved

Count based on another column

  Hi PBI Community, I am trying to solve the following issue. I want to count how many different routes does a Customer follow. In other words, a distinct count of the column "Route" per Customer. ...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    8 years ago

    hello misul,

     

    You could try:

     

    Column = 
CALCULATE(
   DISTINCTCOUNT(Table1[Route]),
   ALLEXCEPT(Table1,Table1[Customer])
   )
  • misul's avatar
    misul
    8 years ago

    ChrisMendoza Correct, the intention is to use DistinctCountofRoutes as a slicer.


    When I use the calculated column, the values are summed. So I put the column to "Don't Summarize" and it seems to work now. Thanks!

     

    Calculated Column: 

    Column = 
CALCULATE(
   DISTINCTCOUNT(Table1[Route]),
   ALLEXCEPT(Table1,Table1[Customer])
   )

     

    Measure:

    Measure= 
CALCULATE(
   DISTINCTCOUNT(Table1[Route]),
   ALLEXCEPT(Table1,Table1[Customer])
   )

     

    I realised that the same formula can be used as a measure too. But as measures cannot be used in Slicers. I will stick with Calculated Column (without summarizing it).

     

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
8 years ago

hello misul,

 

You could try:

 

Column = 
CALCULATE(
   DISTINCTCOUNT(Table1[Route]),
   ALLEXCEPT(Table1,Table1[Customer])
   )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

ChrisMendoza , In this example, if I need to filter the Table1[Route] <> 4, for example, How I can change the formula to Calculate DistinctCount Table1[Route] by Table1[Customer]) but not considering the records with Table1[Route] = 4.

I tried to include the filter in the table but I got the message "Parameter is not the correct type"

In my case mey formula is:

CALCULATE(DISTINCTCOUNT(Suppliers[PART_STATUS]),
ALLEXCEPT( FILTER(Suppliers, RELATED(Suppliers[PART_STATUS])<> "U") ,
Suppliers[KEY]) ).

 

thank you!

 

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    6 years ago

    Anonymous -
    I suppose it depends on what you're trying to accomplish. Reviewing the OP, their need seems different than yours. A calculated column has purpose as well as a measure having it's own purpose.

    Column 2 = 
CALCULATE(
   DISTINCTCOUNT(TableName[Route]),
   ALLEXCEPT(TableName,TableName[Customer]),
   TableName[Route] <> 4
)

    Produces:

    However when you represent it in a visual it likely doesn't produce the result you're looking for. I've included a measure so you can see the differences between [Column] / [Column 2] and a [Measure].

    Measure = 
CALCULATE(
   DISTINCTCOUNT(TableName[Route]),
   ALLEXCEPT(TableName,TableName[Customer]),
   TableName[Route] <> 4
)

    Maybe you could post your question in the forum with your desired outcome if my response doesn't solve your issue?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      ChrisMendoza , thank you very much! It worked using the formula suggested in a new column. Perfect!!! 🙂