cancel
Showing results for
Did you mean:
Regular Visitor

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.

Is this possible to do in a calculated column?

Below is the table and on the right is the desired Count column.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Established Member

Re: Count based on another column

hello @misul,

You could try:

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

Re: Count based on another column

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

10 REPLIES 10
Senior Member

Re: Count based on another column

Hi @misul

What I would do is a global column :

count = COUNTROWS(Table)/CALCULATE(COUNTROWS(Table);ALL(Table))

And then you add both the customer column and the new count column to a Card Box

It will display Customer + Count of distinct

Frequent Visitor

Re: Count based on another column

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

Highlighted
Established Member

Re: Count based on another column

hello @misul,

You could try:

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

Re: Count based on another column

@misul

I've found something usefull if you want to display it in a cardbox : just put both of your column in a multiple line card box and select the distinct option

Regular Visitor

Re: Count based on another column

@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?

Regular Visitor

Re: Count based on another column

@quentin_vigne

Thanks for your help. But I need the Distinct Count as a Column/Measure so that I can use this as a slicer later on.

When I select Distinct Count = 1, show me all the customers which are having 1 route (i.e Customer C,D,E)

When I select Distinct Coutn =4, show me all the customers which are having 4 Routes. (i.e. Customer A)

Any further ideas?

Regular Visitor

Re: Count based on another column

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

Established Member

Re: Count based on another column

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?

Member

Re: Count based on another column

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,