Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
I'd kindly ask for your help, cause I really have no idea how to solve that.
I have two tables where I'd like to find all items for a specific date that are *not* in the table.
Personnel:
Projects:
I want to have a list with all people *not* working on a specific date.
So one option would be to have a resulting list like this:
or of course only the empty items (but this i can achive easy based on the last list):
Date | PNR |
02.01.2021 | 2 |
03.01.2021 | 2 |
03.01.2021 | 3 |
Any idea how to solve this and to get to the third combined list?
thanks in advance
regards
Sebastian
Solved! Go to Solution.
Hi @SebastianAlmer ,
Another way you could tackle this is to create a date table, then cross join your date table with your personnel table, results in one row per day for each employee,
- Create a custom column in your Personnel table in Power Query that simply references the date table like
You would then merge the personnel table with the project table, starting with personnel, on the pnr and date, in Power Query using a full outer join. This leaves you with a table that includes a column with a table object for you to expand.
Expnad the column to get the columns required and you get
Hope that helps,
Proud to be a Super User!
Hi @SebastianAlmer ,
You can create a new calculated table:
Table = ADDCOLUMNS(CROSSJOIN(VALUES(Project[Date]),VALUES(Personnel[PNR])),"Project",CALCULATE(MAX(Project[Project]),FILTER(Project,Project[Date] = EARLIER(Project[Date])&&Project[PNR] =EARLIER(Personnel[PNR]))))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @SebastianAlmer ,
You can create a new calculated table:
Table = ADDCOLUMNS(CROSSJOIN(VALUES(Project[Date]),VALUES(Personnel[PNR])),"Project",CALCULATE(MAX(Project[Project]),FILTER(Project,Project[Date] = EARLIER(Project[Date])&&Project[PNR] =EARLIER(Personnel[PNR]))))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @SebastianAlmer ,
Another way you could tackle this is to create a date table, then cross join your date table with your personnel table, results in one row per day for each employee,
- Create a custom column in your Personnel table in Power Query that simply references the date table like
You would then merge the personnel table with the project table, starting with personnel, on the pnr and date, in Power Query using a full outer join. This leaves you with a table that includes a column with a table object for you to expand.
Expnad the column to get the columns required and you get
Hope that helps,
Proud to be a Super User!
@SebastianAlmercan you try this
table = FILTER(CROSSJOIN(Projects,'Personnel'),[PNR2]<>[PNR])
I worked with this
date | id |
1/1/2020 | 1 |
data
id2 | xyz |
1 | xyz |
2 | xyz |
3 | xyz |
4 | xyz |
id
It worked out for me with
Table = FILTER(CROSSJOIN(data,'id'),[id2]<>[id])
You use the EXCEPT() DAX function for that. Please provide sample data in usable format (not as a picture).
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |