cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tempranello
Helper I
Helper I

Cyclic relationship

Hello there

 

I manage a business that runs a service desk.  Currently there is no integration between our timesheet system and our support ticketing system.  People therefore record their support effort in their weekly timesheets, and as they work on support tickets, they log effort against each journal entry them make.  At a company-wide scale, the total support effort in timesheets is usually greater that the total support effort recorded against Journal entries.

 

If I want to gauge effort against certain ticket-types, or customers, the ticketing system effort will be less than what is being booked in timesheets.  If we assume that timesheets are authoritative, then working our a conversion factor to increase ticketing effort for each week to be more reflective of timesheet data would be useful.

 

To do this, I've a query (Timesheets) which presents manual entries by staff to record their support effort per each week.  Referencing Timesheets, I created a query Timesheet effort by Week, that groups by the week ending date and sums the effort.

 

I do a similar thing with a Journal query and then, referencing it, I create a Journal effort by Week query.

 

I then create a moderation factor lookup query by referencing Timesheet effort by Week, and merge it with Journal effort by week to produce a single table of fields:  Week ending, Timesheet effort, Journal effort.  I then create a custom column, moderation factor by dividing one by the other.  My plan is to be able to multiple any Journal effort figure by this moderation factor to get a better idea of what the real effort is.

 

This is all fine.  However, when I want to use that moderation factor back in the Journal query, all hell breaks loose as I get a cyclic error.  I know that this is becuase moderation factor lookup references Journal effort by week which references Journal.  However, I'm not referencing the same columns.  So this must a an evaluation order problem.  And that has me stumped.

 

I know that this is a complicated description, and the entire premise may appear a little odd. However, this concept using a PowerPivot data model in Excel produces some pretty reliable labour figures.  Bringing this model over to Power BI through power query has fallen over, and I'd appreciate any thoughts you may have.

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Looks totally allowed in my eyes 🙂

That way you break the refresh-chain. Cannot think of any relevant pros/cons here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Super User
Super User

There might be a solution for the order of queries out now, but I'm not aware of it.

 

As a workaround you can continue with (the reference to) your Journal-query within moderation factor lookup query (so instead of ending it, you just create a new step):

 

Journal=Journal,

 

and go on there. As every stepname is a variable, you can just reference your moderation factor by using the last step name you have now.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi there

 

Thanks for that prompt reply.  I'm afraid I don't understand.  Can you explain in a little more detail?

 

I also found a workaround that may get me out of trouble.  It's not ideal as it's using DAX in the main Power BI Desktop window, but it seems to work:

 

Using the Relationships view I created a relation ship from Journal.Week ending to moderation factor lookup.Week ending.  Back into the Desktop's Data view I then added a column: 

 

Moderated effort (hrs) = 'Journal'[Effort]*RELATED('moderation factor lookup'[moderation factor])

 

Is there a disadvantage to using DAX for this?  I feel mildly dirty having stepped out of Power Query to do it, but perhaps that feeling is without basis.

Looks totally allowed in my eyes 🙂

That way you break the refresh-chain. Cannot think of any relevant pros/cons here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks.

 

I'll keep an eye out for further releases of Power BI Desktop to see if the sequencing behaviour changes.  In the meantime I'll stick with my DAX 🙂

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!