cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
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 =
DISTINCT (
SELECTCOLUMNS ('Billing Totals', "CreatedDate", 'Billing Totals'[CreatedDate], "iWeekDay", 'Billing Totals'[iWeekDay])
)

RETURN
ADDCOLUMNS (
CROSSJOIN (TEMPTABLE, VALUES('Billing Totals'[Engineer])),
"Billable Hours Sum", IF (
ISBLANK ( CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) ),
0,
CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) )
),
"Non Billable Sum", IF (
ISBLANK ( CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) ) ,
8,
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]))),
 
)

 

 
model.PNG
 

 

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

 

techreport.PNG

 

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.

 

Best,

1 REPLY 1
Highlighted
Solution Sage
Solution Sage

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

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

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors