You will need to use conditions to solve this.
First I replicated your data using Table():
ClearCollect(table1,
Table(
{Category: "Cat_1", Job: "Job1"},
{Category: "Cat_2", Job: "Job2"},
{Category: "Cat_3", Job: "Job3"},
{Category: "Cat_4", Job: "Job3"},
{Category: "Cat_5", Job: "Job3"},
{Category: "Cat_6", Job: "Job2"}
)
);
ClearCollect(table2,
Table(
{Department: "D1", Job1: "Employee1", Job2: "Employee2", Job3: "Employee3"},
{Department: "D2", Job1: "Employee4", Job2: "Employee5", Job3: "Employee6"},
{Department: "D3", Job1: "Employee7", Job2: "Employee8", Job3: "Employee9"}
)
)The data is already in your app, so you don't need that step.
Next I created ListBox1 and ListBox2. One to house data from table1 and one to hold data from table 2.
You could use a Dropdown or Radio control instead--they all work so you can make a selection.
Create Gallery1. Change its items to include the condition below. The first condition narrows down table2 so that it only shows the department selected in ListBox2:
Filter(table2,Department=ListBox2.Selected.Department)
Add a Label inside Gallery1; the formula below will show the right employee based on the category selected in ListBox1. The Job column in table1 references a column in table2. PowerApps can't understand that it is the name of a column, so a condition is needed to show the right column:
If(ListBox1.Selected.Job="Job1",Job1,
ListBox1.Selected.Job="Job2",Job2,
ListBox1.Selected.Job="Job3",Job3
)
Let me know if this works.
