I need help filtering keywords from one table column into another table

[Jadegirlify]

I need a table showing all employee in FRS table that has also completed each of the training in ICS Table.

Table 1 is called FRS Table with the following details

Employee ID	Employee	Training #	Course Name	Completion Date
1101	May Allen	RT-102	FRS Training	1/1/2024
1102	Jack Ryan	RT-105	FRS Training	2/5/2024
1103	Tom Smith	RT-106	FRS Training	1/31/2024
1104	Mark Toms	RT-109	FRS Training	1/15/2024
1105	John Doe	RT-113	FRS Training	3/30/2024
1106	Mary Hank	RT-118	FRS Training	3/30/2024
1107	Luke Ace	RT-120	FRS Training	1/31/2024
1108	Glen Tom	RT-125	FRS Training	4/30/2024
1109	Jim Park	RT-126	FRS Training	1/30/2024

Table 2 is called ICS Table with the following details

Employee ID	Employee	Training #	Course Name	Completion Date
1101	May Allen	RT-108	ICS10 Training	1/1/2024
1101	May Allen	RT-200	ICS20 Training	3/3/2024
1103	Tom Smith	RT-201	ICS20 Training	4/30/2024
1103	Tom Smith	RT-305	ICS30 Training	4/30/2024
1102	Jack Ryan	RT-315	ICS30 Training	2/5/2024
1101	May Allen	RT-310	ICS30 Training	4/30/2024
1103	Tom Smith	RT-136	ICS10 Training	1/31/2024
1104	Mark Toms	RT-119	ICS10 Training	1/15/2024
1102	Jack Ryan	RT-419	ICS40 Training	1/15/2024
1104	Mark Toms	RT-391	ICS30 Training	5/30/2023
1105	John Doe	RT-123	ICS20 Training	3/30/2024
1106	Mary Hank	RT-158	ICS20 Training	3/30/2024
1107	Luke Ace	RT-180	ICS10 Training	1/31/2024
1108	Glen Tom	RT-175	ICS20 Training	4/30/2024
1109	Jim Park	RT-116	ICS30 Training	1/30/2 024

I need a table that will show if all employee on the FRS table have completed each of the 3 courses in the ICS table (ICS10, ICS20, ICS30) The result should look like this:

Employee ID	Employee	ICSTraining #	ICS10	ICS20	ICS30	ICS Completion Date
1101	May Allen	RT-108	Yes	No	No	1/1/2024
1101	May Allen	RT-200	No	Yes	No	3/3/2024
1103	Tom Smith	RT-201	No	Yes	No	4/30/2024
1103	Tom Smith	RT-305	No	No	Yes	4/30/2024
1102	Jack Ryan	RT-315	No	No	Yes	2/5/2024
1101	May Allen	RT-310	No	No	Yes	4/30/2024
1103	Tom Smith	RT-136	Yes	No	No	1/31/2024
1104	Mark Toms	RT-119	Yes	No	No	1/15/2024
1102	Jack Ryan	RT-419	No	No	No	1/15/2024
1104	Mark Toms	RT-391	No	No	Yes	5/30/2023
1105	John Doe	RT-123	No	Yes	No	3/30/2024
1106	Mary Hank	RT-158	No	Yes	No	3/30/2024
1107	Luke Ace	RT-180	Yes	No	No	1/31/2024
1108	Glen Tom	RT-175	No	Yes	No	4/30/2024
1109	Jim Park	RT-116	No	No	Yes	1/30/2024

I also need a table(Matrix preferrably) that will show all employee on the FRS table that have completed all of the 3 courses in the ICS table (ICS10, ICS20, ICS30). Only 2 employees based on the data provided. The rest have completed one or the other. The result should look like this:

(Thanks so much for your assistance)

Employee ID	Employee	ICS10 #	ICS20 #	ICS30 #	ICS10	ICS20	ICS30
1101	May Allen	RT-108	RT-200	RT-310	Yes	Yes	Yes
1103	Tom Smith	RT-136	RT-201	RT-305	Yes	Yes	Yes

[Ashish_Mathur · Accepted solution]

Hi,

Can you work with this?

[v-kaiyue-msft · Accepted solution]

Hi @Jadegirlify ,

Thanks for the reply from @Ashish_Mathur  and @kpost , please allow me to provide another insight: 

1. Create calculated columns in the ICS table for ICS10, ICS20, and ICS30 completion status.

ICS10 = IF('ICS Table'[Course Name] = "ICS10 Training", "Yes", "No")

ICS20 = IF('ICS Table'[Course Name] = "ICS20 Training", "Yes", "No")

ICS30 = IF('ICS Table'[Course Name] = "ICS30 Training", "Yes", "No")

2. create the calculation table.

Completed_All_Courses = 
FILTER(
    SUMMARIZE(
        'ICS Table',
        'ICS Table'[Employee ID],
        'ICS Table'[Employee],
        "ICS10 #",CALCULATE(MAX('ICS Table'[Training #]),'ICS Table'[Course Name] = "ICS10 Training"),
        "ICS20 #",CALCULATE(MAX('ICS Table'[Training #]),'ICS Table'[Course Name] = "ICS20 Training"),
        "ICS30 #",CALCULATE(MAX('ICS Table'[Training #]),'ICS Table'[Course Name] = "ICS30 Training"),
        "ICS10", CALCULATE(MAX('ICS Table'[ICS10]), 'ICS Table'[Course Name] = "ICS10 Training"),
        "ICS20", CALCULATE(MAX('ICS Table'[ICS20]), 'ICS Table'[Course Name] = "ICS20 Training"),
        "ICS30", CALCULATE(MAX('ICS Table'[ICS30]), 'ICS Table'[Course Name] = "ICS30 Training")       
    ),
    [ICS10] = "Yes" && [ICS20] = "Yes" && [ICS30] = "Yes"
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Ashish_Mathur · Accepted solution]

Hi,

Can you work with this?

[kpost]

Do you have a dimension table containing employees and their IDs with active connection to all of these tables?

If not, that is a good place to start, as it will make combining information from the several tables much easier.

In either case, can you please upload a .pbix file with sample data re-creating the problem?  I think that would answer several other clarifying questions we would have, and enables us to provide a concrete deliverable as the solution.

///Mediocre Power BI Advice, but it's free///