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
BobKoenen
Helper IV
Helper IV

Filter one fact table by another fact table

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

idname
1Joe
3Jane

Fact table Hours

EmployeeProjectHours
Joea8
Joea2
Janea3
Janea5

Fact table Planning

EmployeeProject Hours
Joea8

 

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. 
1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

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.

View solution in original post

3 REPLIES 3
daxer-almighty
Solution Sage
Solution Sage

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.

Thankx!!!

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors