The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |