Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello guys,
I have browsed the forum, but I believe I haven't really found what I'm searching for. The major trouble I have is with Circular referencing, since I want to calculate my resource utilization based on two other calculated columns. (See picture below)
In this table I have columns "Resource" and "Start of use" , which are static. Column "Today's date" is based on formula done in PowerQuery(DateTime.LocalNow())). Column "Capacity (seconds)" and "Worktime total for Resource" formula are done in PowerBI desktop as a calculated column, with formula:
Solved! Go to Solution.
Hi @Anonymous ,
Sorry for some latency, please refer to the following result:
We can change the DAX query for Worktime total for Resource like below:
Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]), FILTER(ALL(Sheet1),Sheet1[Resource] = EARLIER(Resource[Resource])))
Then create the following calculated column:
Utilization % = DIVIDE(Resource[Worktime total for Resource],Resource[Capacity (seconds)]) * 100
Best Regards,
Teige
Hi @Anonymous ,
Your scenario is a common circular dependencies problem, you can try to change the following dax
Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]); ALLSELECTED('Resource'[Resource]))
to:
Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]), 'Sheet1'[**] = ALLSELECTED('Resource'[Resource]))
For more information, please refer to this blog: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
Best Regards,
Teige
Hi @TeigeGao
Would you be able to look into the comment made in earlier post? For some reason I get an error, when trying to do Sheet1**
Hi @Anonymous ,
If you could share the pbix to me, I would get more information about this problem, it will help me troubleshoot it.
Best Regards,
Teige
Hi @Anonymous ,
Sorry for some latency, please refer to the following result:
We can change the DAX query for Worktime total for Resource like below:
Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]), FILTER(ALL(Sheet1),Sheet1[Resource] = EARLIER(Resource[Resource])))
Then create the following calculated column:
Utilization % = DIVIDE(Resource[Worktime total for Resource],Resource[Capacity (seconds)]) * 100
Best Regards,
Teige
Hi @TeigeGao
I tried to change the formula to your suggested format, but for some reason I get an error.
"Column '**' in table 'Sheet1' cannot be found or may not be used in this expression."
What I understood from the formula is that I would try to selected all of the data (columns) in page Sheet1 and have it equal to Resource table column Resource.
I tried to modify the DAX to have it with 1 asteryx, '*', but that didn't work as well.
Do you have any idea, what might be the issue in this case?
Thanks
Ramirenter
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |