Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
Hi everyone!
I would like to create a slicer with and logic. There are some details.
I have a really big table with numbers (of employees) and skills, here languages for simplicity.
Employees:
Number | Language |
5472 | English |
5472 | German |
5472 | Dutch |
5472 | Esperanto |
5472 | Chinese |
4577 | English |
4577 | German |
4577 | Dutch |
4577 | Esperanto |
7289 | English |
9956 | German |
6924 | Japanese |
6924 | Chinese |
I also have a table with other employees' details
Number | Team | Available |
5472 | A | 01.01.2023 |
4577 | A | 13.01.2023 |
7289 | B | 01.02.2023 |
9956 | C | 31.01.2023 |
6924 | C | 01.01.2023 |
Employees and Skills are correlated by number.
Now I want to have slicers for Team, Availability and Languages selection but I want to get only people who know for example English and German and something else.
My solution for AND-filtering is as follows:
* create a table with distinct languages (named Languages)
* create 3 measures
Number of selected languages (Language slicer is on this created table):
NSkillsFiltered = IF( ISFILTERED(Languages[Language]), COUNTROWS(VALUES(Languages[Language])),0)
Number of appearances of certain employee number in the filtered table
NEmployeesWithSelectedSkills =
CALCULATE(
COUNTROWS(FILTER(ALLSELECTED(Skills),Skills[Number]=MAX(Skills[Number])))
)
And a check if these measures agree (so e.g., we have 2 languages selected and an employee appears in the filtered table twice):
NSkillsAgreed = IF([NEmployeesWithSelectedSkills]=[NSkillsFiltered],1,0)
Then I have a filter NSkillsAgreed >= 1 for my visualisation. And that works but in reality Skills table is really big and this solution seems to be rather slow.
Could you help me find something faster and probably more elegant?
Solved! Go to Solution.
@furret You should be able to do this:
Cohort =
VAR tmpTable1 =
GENERATE(
DISTINCT(Employees[Number]),
EXCEPT(
DISTINCT(Employees[Language]),
CALCULATETABLE(DISTINCT(Employees[Language]))
)
)
VAR tmpTable2 = SUMMARIZE(tmpTable1,Employees[Number])
VAR tmpTable3 = EXCEPT(Distinct(Employees[Number]),tmpTable2)
VAR Result = CONCATENATEX(tmpTable3,[Number],",")
RETURN
Result
This is one of the recipes included in my book, DAX Cookbook with a full explanation of how it works.
@Greg_Deckler Your solution looks great and is really fast but I guess I am still a beginner with Power BI and I am struggling to generalize your solution so that I can also display info about availability or team.
Do you have any hints how can I adjust?
@furret You should be able to do this:
Cohort =
VAR tmpTable1 =
GENERATE(
DISTINCT(Employees[Number]),
EXCEPT(
DISTINCT(Employees[Language]),
CALCULATETABLE(DISTINCT(Employees[Language]))
)
)
VAR tmpTable2 = SUMMARIZE(tmpTable1,Employees[Number])
VAR tmpTable3 = EXCEPT(Distinct(Employees[Number]),tmpTable2)
VAR Result = CONCATENATEX(tmpTable3,[Number],",")
RETURN
Result
This is one of the recipes included in my book, DAX Cookbook with a full explanation of how it works.
@furret https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/td-p/391883