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

misul,

Is it summing the column? I wouldn't imagine that you would actually use the calculated column for anything except for some math operation or a slicer.

Based off your response to someone else, your intention is to use a slicer to filter the data set, so the slicer is generating > ~15 routes which you did not expect?

misul's avatar
misul
Helper I
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).