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
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!!! 🙂