Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
Solved! Go to Solution.
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 :
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.
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 :
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.
This is awesome. Thank you so much.