Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SebastianAlmer
Frequent Visitor

Find all items that are not on a list on a specific date

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:

table1.png

 

Projects:

table 2.png

 

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:

table3.png

 

or of course only the empty items (but this i can achive easy based on the last list):

DatePNR
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

2 ACCEPTED SOLUTIONS
richbenmintz
Solution Sage
Solution Sage

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

richbenmintz_0-1611872728010.png

 

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. 

richbenmintz_1-1611872841780.png

Expnad the column to get the columns required and you get

richbenmintz_2-1611872884597.png

Hope that helps,

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

v-deddai1-msft
Community Support
Community Support

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]))))

Capture.PNG

 

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

 

 

View solution in original post

4 REPLIES 4
v-deddai1-msft
Community Support
Community Support

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]))))

Capture.PNG

 

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

 

 

richbenmintz
Solution Sage
Solution Sage

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

richbenmintz_0-1611872728010.png

 

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. 

richbenmintz_1-1611872841780.png

Expnad the column to get the columns required and you get

richbenmintz_2-1611872884597.png

Hope that helps,

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


smpa01
Super User
Super User

@SebastianAlmercan you try this

 

 

table = FILTER(CROSSJOIN(Projects,'Personnel'),[PNR2]<>[PNR])

 

 

 

I worked with this

dateid
1/1/20201

data

 

id2xyz
1xyz
2xyz
3xyz
4xyz

id

 

It worked out for me with

 

 

Table = FILTER(CROSSJOIN(data,'id'),[id2]<>[id])

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
lbendlin
Super User
Super User

You use the EXCEPT() DAX function for that. Please provide sample data in usable format (not as a picture).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.