cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vvibhakar
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:

UserProductCategory
104XYZA1
104ABCA1
104PQRA2
104XYZA2
104ABCA3
237XYZB1
237ABCB2
237DEFB3
237DEFB4
237DEFB4
569PQRC1
569XYZC2
569ABCC2
569DEFC2
569PQRC1

 

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
RicoZhou
Community Support
Community Support

Hi @vvibhakar 

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

For reference: What is the difference between Power BI calculated columns and measures?

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 :

1.png

Select 3 in Value:

1.png

Select A1 in Category:

2.png

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.

 

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

Hi @vvibhakar 

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

For reference: What is the difference between Power BI calculated columns and measures?

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 :

1.png

Select 3 in Value:

1.png

Select A1 in Category:

2.png

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.

 

View solution in original post

This is awesome. Thank you so much. 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Kudoed Authors