Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need calculation filtering data from another table with many to many relationship

Hello All,

It is my first post so please accept appologise if some information may be inconsistent. 


I have started working on report that should provide real data with some technology utilisation statistics including amount of hours, time spend, number of working hours etc. I was able to implement some of requirements by creating Calendar table (Work Days Calendar) and Temporary table (Tech Utilisation) to show key stats but I stumbled on problem with adjustment of working hours based on holiday taken. 


To work out how many days each engineer worked each month I need to substract amount of holiday taken but I cannot achieve this because of type of many to many relationships between

  • Tech Utilisation [FullName] and Leave Planner Enginer  [FullName]
  • Tech Utilisation [Start Date] and Leave Planner [StartDatetime]


So far I got manage to calculate total number of holiday taken using below formula  but I cannot filter data on date ranges. All td does it produces cartesian product so the data is wrong.


Tech Utilisation = VAR TEMPTABLE =
SELECTCOLUMNS ('Billing Totals', "CreatedDate", 'Billing Totals'[CreatedDate], "iWeekDay", 'Billing Totals'[iWeekDay])

CROSSJOIN (TEMPTABLE, VALUES('Billing Totals'[Engineer])),
"Billable Hours Sum", IF (
ISBLANK ( CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) ),
CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) )
"Non Billable Sum", IF (
ISBLANK ( CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) ) ,
IF(CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) >= 8 ), 0, ABS(CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) - 8))
"Time Spent",
IF(ISBLANK(CALCULATE(SUM('Billing Totals'[Hours Spent]))), 0, CALCULATE((SUM('Billing Totals'[TotalTimeSpent]))))/ 60,
"Time Dur",
CALCULATE(SUM('Leave Planner'[LeaveDuration_Quantity])
, FILTER('Leave Planner','Leave Planner'[FullName] = EARLIER('Billing Totals'[Engineer]))),




And  below is a screenshoot of the report with tech stats.




I was trying for few days to find out how diffrently I could make it working but always finished with same result either I could use date slicer or from Leave Planner to be able to filter on date ranges. 


I would appreciate if someone could propose any hint or help me out to find solution.



Solution Sage
Solution Sage

Hi @Anonymous ,

You can only filter the many side of the table relationship on the one side of the table relationship, so you need to use the "Date" in the Calendar table (Work Days Calendar) for filtering. I would like to know what is the formula for "holiday taken". Could you please share your sample data and expected result if you don't have any Confidential Information? Please upload your files to One Drive and share the link here.

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.