Create a filter and a conditional formatting on cell's value of a matrix visual

[ramibelgacem]

Hello,

I'm just starting using Power BI and i have two questions, but i will describe my data before that, so you can understand my situation.

Here is my data 
Here is my two tables in power bi:

1- Employee table

 

2- Duration table

And here is the matrix that i created, employee names in rows and "Year"/"Week number only" in columns:

I applied a conditonal formatting based on the sum of duration_per_day field: if it is blank or value under 5 then color red.

First question :

How can i change the condition of the red cells this way:

highlight in red if :

- Cell is blank

or

- value under 5

- mandate is yes 

- start_date of the employee is after the date of the week

==> based on these condition, the first 2 cells in the matrix for Employee 1 would not be highlighted in red because the Employee 1 have a start date after 2023 and 2024 year.

Second question:

How can i add a filter in the page where i can show on the matrix only the lines that have at least one cell in red.

I already tried to implement some solution for these questions using new measures but it is not working, i'm not sure that i applied it in the right way or if it is even the solution.

Thank you very much for your help.

[ramibelgacem]

Hello,

Anyone can help ?

Thanks

[Anonymous]

Hi @ramibelgacem 

Please provide sample data that fully covers your issue (in the form of table or pbix file) and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

Best Regards,

Jayleny

[ramibelgacem]

Hello @Anonymous , thanks for your response.
I edit my post to include the link to the data file as i can't attach files and the expected output 🙂 

I appreciate your help.