Filter one fact table by another fact table

[BobKoenen]

Hi all, 

I am working on a projectmanagement dashboard. The problem is as follows. I have a Fact table [planning] and a fact table [hours] both are connected to my dim table [employees]. 

Employees

id	name
1	Joe
3	Jane

Fact table Hours

Employee	Project	Hours
Joe	a	8
Joe	a	2
Jane	a	3
Jane	a	5

Fact table Planning

Employee	Project	Hours
Joe	a	8

Now I want a measure to give me the hours from the Hours table for employees that are not planned on that project. The result should be: Jane, a, 8

I have tried the following measure but power bi runs into memory problems:

CALCULATE([Sum of Hours], FILTER(Employees,[sum of planning]= BLANK()))

My Hour fact table contains about 5M rows sow I can imagine this has its limitations. Hope you can help me. 

[daxer-almighty · Accepted solution]

Here's a full solution:

https://1drv.ms/u/s!ApyQEauTSLtOgZlKHry2_uC_CkFTUg?e=Ds6HQO

For simple performant code your model has to be correct (star schema). Let me know if the solution I've given you is fast enough. If not, there is still room to make it faster.

Cheers.

[BobKoenen]

Thankx!!!

[Greg_Deckler]

@BobKoenen Maybe: 

Unplanned Column in Hours =
  VAR __Project = [Project]
  VAR __Employee = [Employee]
  VAR __ProjectEmployees = DISTINCT(SELECTCOLUMNS('Planning',"Employee",[Employee]))
RETURN
  IF(__Employee IN __ProjectEmployees,1,0)