Forum Discussion
Filter created table based on multiple conditions
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!
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!!
8 Replies
Refer to my HR blog https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
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
Hello amitchandak ,
I am sorry i dont quite get you. Could you please provide an example?
Kind regards,
Adham
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.
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 )Adham do you still need help with this, v-frfei-msft 's solution looked pretty solid.
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!!
