Forum Discussion
Creating Job Step Hierarchy for use in table formatting
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:
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.
Hi,
This partially works. I am running into one issue with this column code here:
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 problem is the line in the calculate function calling for MAX(uv_JobOperaitonWeekly[OpComplete]), because its looking at max if even if one column for lets say job A has the criteria of assembly 3 and operation 20 and opcomplete = 1, then this CheckStart1 column shows 1 for all jobs with assembly 0 and operation 60, not just job A, but Job B, Job C, etc.
Keeping with the example I used above, job 006099 with opcomplete = 1 for the criteria above. The CheckStart1 shows 1, but it also shows 1 for all other jobs.
For testing sake, I have been using a TestComplete column that I manually set as true so to show the issue here is my testcomplete column:
This shows that only one row returns as true here, however if I filter my data to show the rows returned using the logic you shared above it returns every row where the operation and assembly match for all the jobs since it uses MAX, even though none of those jobs have an operation complete:
This occurs even when TestComplete is still 0 for the other jobs as you can see in the image. The calculate needs to evaluate per row and not use max. Basically, if you add a couple other job numbers to your test data you will see the issue since right now this only functions if there is one job in the data. I have tried to think of ways to alter these custom columns to work but have not been successful.
How would I alter the logic to make sure its only comparing rows with the same job number?