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

     

shapkovromanBI's avatar
shapkovromanBI
Regular Visitor
8 years ago

Hi,

My solution: 

Create a calculated column

DistinctRoute by customer = 
      VAR CurrencyCustomar = 'Table'[Customar] 
      RETURN
      CALCULATE(DISTINCTCOUNT('Table'[Route]); FILTER(ALL('Table');'Table'[Customar] = CurrencyCustomar))

 

 

 

  • misul's avatar
    misul
    Helper I
    8 years ago

    shapkovromanBI My database is very big, and this formula takes a very very long time to run. This isn't ideal..   formula is still being calculated, it has been a few minutes now.

     

    Update after 10 minutes .. still running. This formula can't be used ..as it takes more than 10 minutes time to run on my database.

    Any further ideas?

  • sscanlon's avatar
    sscanlon
    Helper II
    4 years ago

    Wanted to thank you, your method worked perfect and I was unpivoting columns previously and wanted an easier way. Thank you!