Forum Discussion
Count based on another column
hello misul,
You could try:
Column = CALCULATE( DISTINCTCOUNT(Table1[Route]), ALLEXCEPT(Table1,Table1[Customer]) )
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).
hello misul,
You could try:
Column = CALCULATE( DISTINCTCOUNT(Table1[Route]), ALLEXCEPT(Table1,Table1[Customer]) )
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.
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?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 , In this example, if I need to filter the Table1[Route] <> 4, for example, How I can change the formula to Calculate DistinctCount Table1[Route] by Table1[Customer]) but not considering the records with Table1[Route] = 4.
I tried to include the filter in the table but I got the message "Parameter is not the correct type"
In my case mey formula is:
CALCULATE(DISTINCTCOUNT(Suppliers[PART_STATUS]),
ALLEXCEPT( FILTER(Suppliers, RELATED(Suppliers[PART_STATUS])<> "U") ,
Suppliers[KEY]) ).thank you!
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?
ChrisMendoza , thank you very much! It worked using the formula suggested in a new column. Perfect!!! 🙂
Hi,
This is great. I also tried to use this formula in my dashboard, but unfortunately this didn't helped me much. The problem starts when I apply a filter to other columns.
For e.g. in the given example, Customer A has 4 routes. Let's assume there are other 3 customers - X, Y & Z who also have 4 unique routes. Now if I have a column for region (say EU, APAC, LATAM, etc.) and if I select 1 region, let's say EU, so customer A who has 2 unique routes in EU, should only show 2. But with this formula it still shows 4.
Can you help here please.Thank you so much for the solution.
