Forum Discussion
Displaying Open, Closed, and Backlogged Work Orders
Hi,
Thank you for this.
I tried to replicate your suggested code and ran into a few errors.
First: I tried to create a measure for the Cumulative Open Work Orders but it gave me an error at this stage
YourTable[Date] <= MAX(YourTable[Date])
&& YourTable[Status] = "Open"
)
it did not allow me to select YourTable[Status]. I tried to create a new column instead and this seemed to have worked.
Next when I try to create a measure for the Net Change in Open Work Orders, It comes up with an error
"A single value for column 'Cumulative Open Work Orders' in table 'IW38 - Corr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
When I try to make a new column I get the error
"A single value for column 'Cumulative Open Work Orders' in table 'IW38 - Corr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
I have also tried to add "SUM" before both Cumulative Open Work Orders and recieve error
"<pi>A circular dependency was detected: IW38 - Corr[Net Change in Open Work Orders], IW38 - Corr[Cumulative Open Work Orders], IW38 - Corr[Net Change in Open Work Orders].</pi>"
I am not sure what the next steps would be.
Memorable Member1. Creating the Cumulative Open Work Orders Measure
To create a measure for the cumulative open work orders, you can use the following DAX formula:
Cumulative Open Work Orders =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
ALL(YourTable),
YourTable[Date] <= MAX(YourTable[Date])
&& YourTable[Status] = "Open"
)
)
Make sure that YourTable[Status] is a valid column in your table. If it doesn't allow you to select it, double-check the column name and ensure it exists in the table.
2. Creating the Net Change in Open Work Orders Measure
For the net change in open work orders, you need to ensure that you're aggregating the values correctly. Here's a revised approach:
Net Change in Open Work Orders =
SUMX(
YourTable,
IF(
YourTable[Status] = "Open", 1,
IF(YourTable[Status] = "Completed", -1, 0)
)
)
3. Avoiding Circular Dependencies
Circular dependencies occur when measures or calculated columns depend on each other in a way that creates a loop. To avoid this, you need to ensure that your calculations are independent of each other.
Example Implementation
Create a Calculated Column for Status Change:
Status Change =
IF(
YourTable[Status] = "Open", 1,
IF(YourTable[Status] = "Completed", -1, 0)
)
Create a Measure for Cumulative Open Work Orders:
Cumulative Open Work Orders =
CALCULATE(
SUM(YourTable[Status Change]),
FILTER(
ALL(YourTable),
YourTable[Date] <= MAX(YourTable[Date])
)
)
Create a Measure for Net Change in Open Work Orders:
Net Change in Open Work Orders =
SUM(YourTable[Status Change])
Final Steps
Plot the Data: Use the Cumulative Open Work Orders measure as your line in the chart.
Bars for Monthly Changes: Use the Net Change in Open Work Orders measure for the bars representing monthly changes.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!