cancel
Showing results for
Did you mean:
Frequent Visitor

## Distinct Count of a column based on another column with data filter capability

Hi,

I need help in creating a calculated column to calculate distinct count of 1 column based on another. Below is the sample data I have:

 User Product Category 104 XYZ A1 104 ABC A1 104 PQR A2 104 XYZ A2 104 ABC A3 237 XYZ B1 237 ABC B2 237 DEF B3 237 DEF B4 237 DEF B4 569 PQR C1 569 XYZ C2 569 ABC C2 569 DEF C2 569 PQR C1

In this case, User 104 & 237 has 3 distinct products and user 569 has 4 distinct products.
So I'd like to have a calculated column were 3 & 4 are mentioned and I can use it in Chiclet slicer, so when I click on 3 I get Users 104 & 237 as output.

So I used the formula :

CALCULATE(DISTINCTCOUNT(Table[Product]),ALLEXCEPT(Table,Table[User]))

This gives me perfect result.
But now on the report I want to apply filter on Category, say A1, then for User 104, distinct product should only show 2. But the above formula still shows 3.

Any help is appreciated.

Cheers.
Vivek

1 ACCEPTED SOLUTION
Community Support

Calculated column can not be dynamic impacted by slicer, but measure can.

Here I suggest you to create a table with whole number you want to select in slicer ,then create a count measure and filter your user result by another filter measure based on this count measure.

1. Create a table for slicer.

``Count Slicer = GENERATESERIES(1,5) ``

2.Create measures.

``DISTINCTCOUNT = CALCULATE(DISTINCTCOUNT('Table'[Product]),ALLEXCEPT('Table','Table'[User],'Table'[Category]))``
``Filter = IF(ISFILTERED('Table'[Category]),1,IF([DISTINCTCOUNT] in VALUES('Count Slicer'[Value]),1,0))``

Create a table visual by User column and [DISTINCTCOUNT] measure, then add [Filter] measure into filter field in this visual and set it to show items when value =1. Result is as below.

By default :

Select 3 in Value:

Select A1 in Category:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Calculated column can not be dynamic impacted by slicer, but measure can.

Here I suggest you to create a table with whole number you want to select in slicer ,then create a count measure and filter your user result by another filter measure based on this count measure.

1. Create a table for slicer.

``Count Slicer = GENERATESERIES(1,5) ``

2.Create measures.

``DISTINCTCOUNT = CALCULATE(DISTINCTCOUNT('Table'[Product]),ALLEXCEPT('Table','Table'[User],'Table'[Category]))``
``Filter = IF(ISFILTERED('Table'[Category]),1,IF([DISTINCTCOUNT] in VALUES('Count Slicer'[Value]),1,0))``

Create a table visual by User column and [DISTINCTCOUNT] measure, then add [Filter] measure into filter field in this visual and set it to show items when value =1. Result is as below.

By default :

Select 3 in Value:

Select A1 in Category:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

This is awesome. Thank you so much.

Announcements