Forum Discussion
Creating Job Step Hierarchy for use in table formatting
This may be a hard question to explain but I will do my best.
I have a report that has the following information in a table:
What this shows is our job operations (or steps) in grey when the job is on hold, due date in red when the due date is past, and some other information I cut off that is not important.
I have been requested to add a new feature that highlights the job number when the previous operation step that signals the next one to start is completed, signifying that job/operation needs to begin.
The problem I have is the order of steps from operation to operation are not in order and im not sure how to accomplish this in BI.
Using job 006099 as an example, here is the raw data:
The chain works as follows:
When assembly 3 operation 20 is completed -> assembly 0 operation 60 starts
When assembly 0 operation 60 is complete -> assembly 0 operation 80 starts
When assembly 0 operation 80 is complete -> assembly 0 operation 150 starts
When assembly 0 operation 20 is complete -> assembly 0 operation 50, 110, 120, 130, and 140 should start
So to complete the example, if assembly 0 operation 60 was marked complete, then assembly 0 operation 80 for job 06099 on the table would be highlighted in a color, and so on.
The problem im trying to solve is how to write this into BI, then have each line of the data able to check if they should be starting so I can use this to format the job number column and highlight it.
It looks like I resolved the issue I was having, rewriting the code a little to use variables instead and grab the job for comparisons.
Here is what I am using now if youre interested:
6 Replies
Hi AppleMan
According to your case description, we may understand that you want to set conditional formatting for your table.
However, I have some doubts that I want to confirm with you. Could you please explain in more detail about how it works? For example, how do we determine that when “assembly 3 operation 20” is completed?
Here is the link to set the conditional format for your reference: Apply conditional table formatting in Power BI - Power BI | Microsoft Learn
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good question, you would use the "OpComplete" column in the data I shared above. If this is 1 then that corresponding assembly and operation number is done. In my head, I would think its best to have a column in each row that is either 1 or 0 showing if that row is ready to start based on the guide I shared.
This is not just simple conditional formatting of looking at a row and doing formatting based on that. I need to look at a different row to decide the formatting for the current row in the table, which is where my trouble is.
For example, Jobnum 006099 in the picture above, if the opcomplete column was "1" for the second row that would mean assembly 0 operation 20 is complete. So going off the guide I also shared, the rows for jobnum 006099 for assembly 0, operation 50, 110, 120, 130, and 140 would have an "OperationComplete" column with a 1 in it that I can use for conditional formatting.
After sleeping on this it may be possible with a switch statement custom column.
This code here partially works:
The problem is, the second part each switch, the if statement, cannot be found as true since it is looking at the row the first part of the switch statement goes to. I need to find a way to have the second part of each switch or the if statement look at the previous job operation if that makes sense.
The other problem will be ensuring this column logic is only looking at columns where the job number matches. Right now I was testing this using one job just to get the first part working.
Hi AppleMan
Thanks for the further clarification. My suggestion would be to create a column for each rule to determine if it is start or not. I hope my method below is helpful to you.
1. Here is my test data, the “OpComplete” column is used to indicate completion. Please refer to the uploaded pbix file.2. Based on the “OpComplete” column, create a new column for each rule to indicate whether the other rows start. The DAX is as follows:
CheckStart1 = IF( uv_JobOperationWeekly[AssemblySeq] = 0 && uv_JobOperationWeekly[OprSeq] = 60 && CALCULATE(MAX(uv_JobOperationWeekly[OpComplete]), FILTER( uv_JobOperationWeekly, uv_JobOperationWeekly[AssemblySeq] = 3 && uv_JobOperationWeekly[OprSeq] = 20) ) = 1, 1, 0 )The measures for the other rules are similar, except that they replace the “Assembly” and "OprSeq" value with a different one.
3. Creates a new column that returns the color based on the “CheckStart” columns.
Color = SWITCH( TRUE(), uv_JobOperationWeekly[CheckStart1] = 1, "green", uv_JobOperationWeekly[CheckStart2] = 1, "red", uv_JobOperationWeekly[CheckStart3] = 1, "blue", uv_JobOperationWeekly[CheckStart4] = 1, "yellow", "white" )
4. Create a table visual and select a column to apply conditional formatting.
In the Conditional Format window, select “Field value” as the “Format Style”, in the “What field should we base this on?” drop-down list, select the “Color” column.Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
