Creating a Slicer with AND Function

[Anonymous]

Hi everyone!

This is my first post to Power BI so forgive me if I put this in the wrong area.  I am also new to Power BI.  I have created a report that has a slicer (contains Categories) and another table that has Employee Name and Cartegorgies/Skill.  In the slicer, I would like for when a person selects multiple options that it shows all criteria matching (AND function) showing what was selected.  

I have done some research but it appears every answer is for when its in multiple tables.  All of my data is in the same data source and is shown below

Column A:  Employee Name

Column B:  Categories / Skills

Column C:  Category

Can someone help me on how I can accomplish the AND function when selecting multiple criteria?

Pep

[lukiz84]

perfect i will try with those data and then upload my pbix file, give me a few minutes 😉

[lukiz84]

Strange, that's exactly what I did and where i made the screenshots from which show it's working?!

1) And you did not set any relationship between "Calculated" and "Skills Master")?

2) What exactly does not work?

BR

[Anonymous]

Hi Lukiz,

I have checked the relationships and they are not related.  As far as whats not working, it does not seem to be choose all the AND's.  For example I chose one person who has a Masters Degree, but they showed up when choosing other skills.

[lukiz84]

thats very strange, can you post another table with e.g. 20 demo rows for your Skills Master table? You can change the employee names to random names so you have no privacy problem... maybe share it as a table which i can copy to excel

[Anonymous]

Yes of course.  Excuse the names, my co-worker was all yippy skippy in helping me make up names

Employee Name	Certification (Skill Level)	Certification
Snuffy, John	PSS Adjudication Certification (Beginner)	PSS Adjudication Certification
Doe, John	PMP Certification (Expired (Certification Only))	PMP Certification
Doe, Jane	PMP Certification (Beginner)	PMP Certification
Doe, Scott	DU Certification (Beginner)	DU Certification
Johns, James	DEU Certification (Active (Certification Only))	DEU Certification
Sacker, James	DEU Certification (Expired (Certification Only))	DEU Certification
Sacker, Alice	DEU Certification (Active (Certification Only))	DEU Certification
McDaniels, Fluffy	Employee Relations (Intermediate)	Employee Relations
Snuffy, John	Employee Relations (Beginner)	Employee Relations
Doe, John	Employee Relations (Intermediate)	Employee Relations
Doe, Jane	Equal Employmnet Opportunity (Advanced)	Equal Employmnet Opportunity
Doe, Scott	Equal Employmnet Opportunity (Beginner)	Equal Employmnet Opportunity
Johns, James	Equal Employmnet Opportunity (Intermediate)	Equal Employmnet Opportunity
Snuffy, John	Agency Staffing Policies and Procedures (Intermediate)	Agency Staffing Policies and Procedures
Chaps, Roger	Application Processing (JOAs, Vas, etc) (Beginner)	Application Processing
Hatcher, Margaret	Assessment Methods and Tools (Beginner)	Assessment Methods and Tools
Xavier, Charles	Agency Staffing Policies and Procedures (Intermediate)	Agency Staffing Policies and Procedures
Gray, Jean	Application Processing (JOAs, Vas, etc) (Intermediate)	Application Processing
Sacker, Alice	Assessment Methods and Tools (Intermediate)	Assessment Methods and Tools
McDaniels, Fluffy	Qualification Analysis (Intermediate)	Qualification Analysis
Snuffy, John	Veterans' Preference (Intermediate)	Veterans' Preference
McDougall, Slim	Agency Staffing Policies and Procedures (Expert)	Agency Staffing Policies and Procedures
Wayne, Bryce	Application Processing (JOAs, Vas, etc) (Advanced)	Application Processing
Wayne, Richard	Assessment Methods and Tools (Beginner)	Assessment Methods and Tools
Sacker, James	HR Call Center (Expert)	HR Call Center
Hatchett, Holly	HR Call Center (Beginner)	HR Call Center
Munster, Lily	HR Call Center (Advanced)	HR Call Center
Daphne, Fred	HR Call Center (Beginner)	HR Call Center

[lukiz84 · Accepted solution]

Hi, i've added another matrix with a different behaviour, please see the pbix file

https://1drv.ms/u/s!AjI-wCUgwzyu8SlfXXLz6DACVwhm?e=RhIp1f

[Anonymous]

Hi, you are so awesome!  Thank you so much for your help, I really appreciate it.  

[lukiz84]

was a nice practice for me, too 😄

[lukiz84]

Ah ok, now I see, that wasn't clear from the question.

2 steps,

1) Create a disconnected table with no relationship to your data, (Calculated table with code)

Certifications = VALUES(TableData[Certification])

2) Create a FilterMeasure with code:

FilterMeasure = 
    VAR EmployeeCerts = 
        CALCULATETABLE(
            VALUES(TableData[Certification]),
            ALLEXCEPT(TableData, TableData[Employee]),
            TREATAS(VALUES(Certifications[Certification]), TableData[Certification])
        )
    
    RETURN
        IF(COUNTROWS(EmployeeCerts) = COUNTROWS(Certifications), 1, BLANK())

and add this measure to your matrix filters with "is not blank"

[Anonymous]

Hi Lukiz!  Thank you again so much.  I have a couple of Questions.  Due to my inexperience in Power BI I have some questions and I am sorry to ask such basic questions.

1.  When creating the Disconnected table I am going into modeling a creating a new table.  However when doing so it shows "1 Table =".  Do I need to put anything there?

2.  Do I need to name any columns in this disconnected table?  I understand no data but not sure of the column names

3.  When creating the Filter Measure, do I create that in the new table or the original table?  

4.  When doing the matrix filter do I put that in the new or original table?

Again i am sorry for the questions and thank you so much for your help.  Pep

[Anonymous]

Hi Lukiz!

So I did everything and its not working.  I am sure its users error.  Below is what I did

1.  The Disconnected Table is named "Calculated"

2.  The Main Data Table is named "Skills Master"

3.  I put the following formula in the Calculated Table:  

Calculated = VALUES('Skills Master'[Certification])

4.  I put the Filter Measure in the Main Data Table ("Skills Master")

FilterMeasure =

    VAR EmployeeCerts =

        CALCULATETABLE(

            VALUES('Calculated'[Certification]),

            ALLEXCEPT('Skills Master','Skills Master'[Employee Name]),

            TREATAS(VALUES('Calculated'[Certification]), 'Skills Master'[Certification])

        )

    RETURN

        IF(COUNTROWS(EmployeeCerts) = COUNTROWS('Calculated'), 1, BLANK())

I thought it was all good, but its not working.

[lukiz84]

Hi, don't be sorry, it's ok to be interested 🙂

1) Yes, Modeling -> Create new table. The Code has to be input there 

VALUES(YourDataTable[Certification])

2) No, it's Certfication by default, because of the Code above

3) Doesn't matter where the measure is. You can put it anywhere.

4) The filter has to be set on the matrix where you want to show the employees and certifications, see my screenshot 🙂

BR

[Anonymous]

Hi Lukiz,

Thank you so much.  I will work on this today between meetings :).  I will let you know how it goes.

[Anonymous]

Awesome thank you so much.  I will give this a try!

[lukiz84]

If those are the results you except, then it works 🙂

none selected (returns an empty table):

one selected which has 2 employees:

[Anonymous]

Hi Lukiz,

All the data is in the same table but when I chose multiple Certifications (which is shown in Column C) I want only the employee name show up thats matching all the Certifications.  For example, if I select Classification and Adjudication Certification the only name that would show is Doe, John.  Does this help?

[lukiz84]

Hi,

So in this table there is the Certification? And there's another table with only the Certifications? Then just connect the Certifications-Table to the Certification Column in your other table (1:n) => Slicer on the "Certifications-Table" and if you do a multi select it shows all entries from the other table...

[Anonymous]

Hi Lukiz!

I have pasted some data below.  Thank you so much for your help!

Employee	Certification / Skill Level	Certification
Doe, John	Adjudication Certification (Advanced)	Adjudication Certification
Doe, Jane	DU Certification (Beginner)	DU Certification
Doe, Scott	Classification (Beginner)	Classification
Doe, John	Classification (Expert)	Classification
Doe, Jane	Employee Relations (Intermediate)	Employee Relations

[lukiz84]

Can you post some sample data of the table?

[Anonymous]

Sorry here is a snippet of the data that came out skewed