How do I create a list of values based on a slicer selection, so I can then populate a table?

[cw900]

I have a list of groups and a list of rules based on job title. The rules don't always follow the same format so I can't just join the two tables in the model and select from the slicer to fill the table.

The end results is that I want to select a an Employee Grouping in a slicer and display the employees in a table. I'd be happy to create some list of nested if statements and iterate through each rule or similar but I don't know where I would evaluate that list of nested if statements.

These are the two tables of data I'm working with:

Employee Grouping	Rule
Leadership	JobTitle = "Leadership"
Managers	JobTitle = "Manager"
Factory Staff	JobTitle = "Factory"
Admin Staff	JobTitle IS LIKE "Admin"
Temp Staff	JobTitle IN ("Temp Office", "Temp Factory")

Employee ID	Employee Name	Employee JobTitle
2340	Sarah	Leadership
2342	Bob	Leadership
3456	Janice	Temp Factory
4564	Jane	Factory
5727	Steve	Admin Assistant
2457	Linda	Factory
3453	Pete	Temp Office
2341	Scott	Factory
7456	Tom	Factory
6456	Gill	Admin Lead
2635	Bert	Manager
2178	Tracy	Manager

[v-zhangti · Accepted solution]

Hi, @cw900 

You can try the following methods.

Column:

Employee Grouping = 
SWITCH(TRUE(),
[Employee JobTitle]="Leadership","Leadership",
[Employee JobTitle]="Manager","Managers",
[Employee JobTitle]="Factory","Factory Staff",
SEARCH("Admin",[Employee JobTitle],,BLANK())<>BLANK(),"Admin Staff",
[Employee JobTitle] in {"Temp Office", "Temp Factory"},"Temp Staff")

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Write this calculated column formula

Column = if(SEARCH("admin",Data[Employee JobTitle],,BLANK()),"Admin",if(SEARCH("temp",Data[Employee JobTitle],,BLANK()),"Temp",Data[Employee JobTitle]))

Hope this helps.