Forum Discussion

furret's avatar
furret
New Member
3 years ago
Solved

Quick slicer with AND

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:

NumberLanguage
5472English
5472German
5472Dutch
5472Esperanto
5472Chinese
4577English
4577German
4577Dutch
4577Esperanto
7289English
9956German
6924Japanese
6924Chinese

 

I also have a table with other employees' details

NumberTeamAvailable
5472A01.01.2023
4577A13.01.2023
7289B01.02.2023
9956C31.01.2023
6924C01.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?

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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.

     

3 Replies

  • furret's avatar
    furret
    New Member
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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.