Quick slicer with AND

[furret]

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?

[furret]

@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?

[Greg_Deckler · Accepted 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]

@furret https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/td-p/391883