Filter created table based on multiple conditions

[Adham]

Hello All,

I hope you are all well. I have got the following two tables:

Tasks (Table 1):

Employee ID | Project ID | Task Start Date | Task Due Date

different employees can work on different projects and different projects will have multiple people working on them.

Employee Weeks (Table 2):

Date | Employee ID | Project ID | Work Hours

Table 2 is created using Table 1. The code i used is:

Employee Weeks = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GENERATE(
                FILTER(
                    ADDCOLUMNS(
                        GENERATE(
                            DISTINCT(Tasks[Task Assignee Id]),
                            CALENDAR(MIN(Tasks[Task Start Date]),MAX(Tasks[Task Due Date]))
                        ),
                        "__IsWeekDay",IF(WEEKDAY([Date],3) < 5,TRUE(),FALSE())
                    ),
                    [__IsWeekDay] = TRUE()
                ),
                DISTINCT(Tasks[Project Id])
            ),
            "__Work Hours",8
        ),
        "Employee Id",[Task Assignee Id],
        "Project Id", [Project Id],
        "Date",[Date],
        "Work Hours",[__Work Hours]
    )

I want to filter table 2 to only have values where:

if a row in table 1 has equal values of employee id and project id to a row in table 2 and also for the date of the row of table 2 to be between the start and due date of the row of table 1.

I would really appreciate if someone can help me on this!

[v-frfei-msft]

Hi @Adham ,

Please try to get the filtered table by the following formula. If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Employee Weeks =
VAR k =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                FILTER (
                    ADDCOLUMNS (
                        GENERATE (
                            DISTINCT ( Tasks[Task Assignee Id] ),
                            CALENDAR ( MIN ( Tasks[Task Start Date] ), MAX ( Tasks[Task Due Date] ) )
                        ),
                        "__IsWeekDay", IF ( WEEKDAY ( [Date], 3 ) < 5, TRUE (), FALSE () )
                    ),
                    [__IsWeekDay] = TRUE ()
                ),
                DISTINCT ( Tasks[Project Id] )
            ),
            "__Work Hours", 8
        ),
        "Employee Id", [Task Assignee Id],
        "Project Id", [Project Id],
        "Date", [Date],
        "Work Hours", [__Work Hours]
    )
VAR std =
    CALCULATE (
        MAX ( Tasks[Task Start Date] ),
        FILTER ( Tasks, [Employee ID] = [Employee Id] && [Project ID] = [Project Id] )
    )
VAR endd =
    CALCULATE (
        MAX ( Tasks[Task Due Date] ),
        FILTER ( Tasks, [Employee ID] = [Employee Id] && [Project ID] = [Project Id] )
    )
RETURN
    FILTER ( k, [Date] >= std && [Date] <= endd )

[Greg_Deckler]

@Adham do you still need help with this, @v-frfei-msft 's solution looked pretty solid. 

[Adham · Accepted solution]

Hello @Greg_Deckler  and @v-frfei-msft ,

Thank you very much for your help. I actually solved it by running a python script since i am more proficient in python than DAX. Its really cool how power BI enables the use of python!!

[amitchandak]

Refer to my HR blog https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

There us current employee calculation. Take the filter part of it and move it inside the calculate table.

On top of that put a summarize and get the data

[Adham]

Hello @amitchandak ,

I am sorry i dont quite get you. Could you please provide an example?

Kind regards,

Adham

[amitchandak]

Try something like this. Not tested

summarize(
 CALCULATETABLE(Employee,filter(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),CROSSFILTER(Employee[Start Date],'Date'[Date],None)),Employee[ID],Employee[Project Id],'Date'[Date],"__Work Hours",8)

You might have to remove crosstable and date table join.

[Adham]

I am actually more confused now

[JirkaZ]

@Adham You'll have to create a measure to calculate whatever you need to calculate from table 2. This cannot be done using relationships and modelling.