topic Re: Creating a Slicer with AND Function in DAX Commands and Tips

Creating a Slicer with AND Function

Anonymous — Tue, 01 Nov 2022 15:02:50 GMT

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

Re: Creating a Slicer with AND Function

lukiz84 — Tue, 01 Nov 2022 15:42:06 GMT

Can you post some sample data of the table?

Re: Creating a Slicer with AND Function

Anonymous — Tue, 01 Nov 2022 15:54:17 GMT

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

Re: Creating a Slicer with AND Function

Anonymous — Tue, 01 Nov 2022 15:55:53 GMT

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

Re: Creating a Slicer with AND Function

lukiz84 — Tue, 01 Nov 2022 16:08:33 GMT

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...

Re: Creating a Slicer with AND Function

Anonymous — Tue, 01 Nov 2022 16:13:47 GMT

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?

Re: Creating a Slicer with AND Function

lukiz84 — Tue, 01 Nov 2022 17:18:45 GMT

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"

Re: Creating a Slicer with AND Function

Anonymous — Tue, 01 Nov 2022 17:21:25 GMT

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

Re: Creating a Slicer with AND Function

lukiz84 — Tue, 01 Nov 2022 17:23:36 GMT

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

none selected (returns an empty table):

one selected which has 2 employees:

Re: Creating a Slicer with AND Function

Anonymous — Tue, 01 Nov 2022 19:37:31 GMT

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

Re: Creating a Slicer with AND Function

lukiz84 — Wed, 02 Nov 2022 07:06:33 GMT

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 🙂

Re: Creating a Slicer with AND Function

Anonymous — Wed, 02 Nov 2022 11:55:42 GMT

Hi Lukiz,

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

Re: Creating a Slicer with AND Function

Anonymous — Wed, 02 Nov 2022 15:06:55 GMT

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.

Re: Creating a Slicer with AND Function

lukiz84 — Wed, 02 Nov 2022 15:09:08 GMT

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?

Re: Creating a Slicer with AND Function

Anonymous — Wed, 02 Nov 2022 15:21:22 GMT

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.

Re: Creating a Slicer with AND Function

lukiz84 — Wed, 02 Nov 2022 15:41:26 GMT

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

Re: Creating a Slicer with AND Function

Anonymous — Wed, 02 Nov 2022 16:07:11 GMT

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

Re: Creating a Slicer with AND Function

lukiz84 — Wed, 02 Nov 2022 17:31:59 GMT

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

Re: Creating a Slicer with AND Function

lukiz84 — Wed, 02 Nov 2022 17:44:54 GMT

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

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

Re: Creating a Slicer with AND Function

Anonymous — Wed, 02 Nov 2022 17:54:08 GMT

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