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])
   )
misul's avatar
misul
Helper I
8 years ago

ChrisMendoza Thanks for your idea. This is working in my dummy file. But not in my database (which has much more records). 

 

There are only 15 routes available. But the measure you gave is returning values like - 3000, 200 etc.

 

Any ideas? 

Appreciated.

  • 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).

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello Dozer,

       

      Since you are specifying it takes more time to a computer. Can you split the main table to have only "customer" and "route" and later join the new table by the customer and give appropriate direction?

       

       

      Thanks,

      Aditya