If a value is present in one row leave other related rows blank

[GunnerJ]

I'm currently determining the length of sequences within service orders. My time starts from the date/time of the first task the has WORKGRP3 = "SERVICE" and ends at the date/time of the last task. In some service orders a delay can occur in field "BI_WORK_EVENT_CD2". I'm hoping to modify some part of my code to leave all times for any row with the same sequence number of a service order blank if any row contains "DELAY". 

In the picture below you'll see an example of one sequence number within a service order. The 3rd task (BI_WORK_EVENT_CD2) is set as delay. I want the last column of task time to return blank for every row of this sequence number.

If no Delay is present then I want the calculation to perform normally by calculating the time between the first "Service" in workgroup3 and the last BI_EVENT_DT_TM.

Here is my code so far. 

* Determines the minimum time by looking for the service in workgrp3.*

02.Min.Task Time by SO# = CALCULATE(min(Workflow[BI_EVENT_DT_TM]),allexcept(Workflow,Workflow[BI_SO_NBR],Workflow[BI_WRKFLW_TASK_SEQ_NBR],Workflow[BI_WORK_EVENT_CD]),Workflow[BI_WORKGRP3]="SERVICE")

*Finds the max date/time*

03.Max.Task Time by SO# = CALCULATE(max(Workflow[BI_EVENT_DT_TM]),allexcept(Workflow,Workflow[BI_SO_NBR],Workflow[BI_WRKFLW_TASK_SEQ_NBR],Workflow[BI_WORK_EVENT_CD]))

*Finds the time between those two calculations assuming the event_cd = COMP. Note that there's a difference between BI_WORK_EVENT_CD and BI_WORK_EVENT_CD2.*

04.Task Time = if(Workflow[BI_WORK_EVENT_CD]="COMP" && Workflow[02.Min.Task Time by SO#]>0 && Workflow[BI_SO_NBR]>0,Workflow[03.Max.Task Time by SO#]-Workflow[02.Min.Task Time by SO#],0)

Any help would be greatly appreciated. If any further information is needed I'd be happy to provide it.

Thank you

[Cmcmahan]

Sure.  Quick and dirty solution would be to do something like this:

Task Time Blank on Delay = 
IF( CONTAINS( 
        ALLEXCEPT(Workflow, Workflow[BI_SO_NBR]),
        Workflow[BI_WORK_EVENT_CD2],
        "DELAY" ),
    BLANK(),
    [04.Task Time] )

This is taking the table Workflow, but removing all filters except those on BI_SO_NBR, so that we're still checking within the same group.  Then it's checking the resulting BI_WORK_EVENT_CD2 column and checking if it contains "DELAY".  If it does, leave it blank, if it doesn't, give it a value.

[GunnerJ]

@Cmcmahan Thanks for the reply. I'm all for any solution that'll get the end result. I've tested your code but for some reason it causes the rest of the data to disappear. I'm hoping for and assuming your intent was to has that column just show up blank for the service orders that had Delay within them.

Can you think of any reason the other columns might go away when adding that column?

[Cmcmahan]

Is the data going away, or are you suddenly getting a VERY tall table, with a bunch of 0s for this measure?

If you scroll down the visual (it might take a lot of scrolling, depending on the size of your dataset) do you eventually see data entries?

[Cmcmahan]

I'm not sure if it would fix it, but you might be able to use ALLSELECTED instead of ALLEXCEPT:

Task Time Blank on Delay = 
IF( CONTAINS( 
        ALLSELECTED(Workflow),
        Workflow[BI_WORK_EVENT_CD2],
        "DELAY" ),
    BLANK(),
    [04.Task Time] )

This will likely break if you show multiple BI_SO_NBRs at the same time and one of them has a delay, since ALLSELECTED changes the context to all data in the visual, but it might do the trick if you're forcing slicer selection of one BI_SO_NBR at a time.

[Cmcmahan]

Oh, absolutely zero data is weird.  Do you have the visual/report set up to filter out rows where there's blank data?

You could try being more explicit about the filter in the expression:

Task Time Blank on Delay = 
CALCUALTE(
    IF( CONTAINS( 
            Workflow,
            Workflow[BI_WORK_EVENT_CD2],
            "DELAY" ),
        BLANK(),
        [04.Task Time] ),
    FILTER(ALL(Workflow), Workflow[BI_SO_NBR] = SELECTEDVALUE(Workflow[BI_SO_NBR]) )
)

[GunnerJ]

I created a new page and just tried to fill a table with data. Still when I added the new column everything disappeared. 

Does your latest code require I use measures? 

Also, is there anyway to modify the code I already have to leave the sequence numbers with delay blank? I'm just trying to see if we have any other paths to a final solution. 

Thank you for being so responsive!

[Cmcmahan]

I'm assuming that all of the code you've listed has been measures.  If you're creating all of these as calculated columns, then you need to make minor edits to the original code to use EARLIER instead of assuming context:

Task Time Blank on Delay = 
IF( CONTAINS( 
        FILTER(ALL(Workflow), [BI_SO_NBR] = EARLIER([BI_SO_NBR])),
        Workflow[BI_WORK_EVENT_CD2],
        "DELAY" ),
    BLANK(),
    [04.Task Time] )

[GunnerJ]

@Cmcmahan Ok this is almost what I need. Only change needed is to only give a blank for the particular sequence number of a service order. Right now it's giving a blank for the entire service order. In the example below the final column should replecate all of the values shown in 04.Task Time except SEQ_NBR 25 which has a delay in one of it's tasks. Is this possible?

[Cmcmahan]

OHHHH. In your original post you said:

---

@GunnerJ wrote:

I want the last column of task time to return blank for every row of this sequence number

I took that to mean BI_SO_NBR was the sequence number, and didn't even consider BI_WRKFLW_TASK_SEQ_NBR. 

This will make you a column where only sequence 25 is blank.

Task Time Blank on Delay = 
IF( CONTAINS( 
        ALLEXCEPT(Workflow, Workflow[BI_SO_NBR], Workflow[BI_WRKFLW_TASK_SEQ_NBR]),
        Workflow[BI_WORK_EVENT_CD2],
        "DELAY" ),
    BLANK(),
    [04.Task Time] )

[GunnerJ]

@Cmcmahan So I'm using calculated columns and the code you sent for that was almost what I needed. I tried to add [SEQ_NUM] = earlier [SEQ_NUM] but got the error that only two arguements were allowed in the filter. If there's a way to add sequence number (BI_WRKFLW_TASK_SEQ_NBR) to the code below it'd be perfect. I tried the last code you sent and for some odd reason my data disappears still. 

Task Time Blank on Delay = 
IF( CONTAINS( 
        FILTER(ALL(Workflow), [BI_SO_NBR] = EARLIER([BI_SO_NBR])),
        Workflow[BI_WORK_EVENT_CD2],
        "DELAY" ),
    BLANK(),
    [04.Task Time] )

[Cmcmahan]

Huh, weird.  Using ALLEXCEPT should have fixed that context.  Since ALLEXCEPT is just shorthand for keeping filters, you can definitely remove them with ALL and then re-apply them afterwards. Multiple ways to solve every problem and all that.

In order to filter on multiple conditions within a single FILTER statement, you need to use && to logically AND the two conditions. 

Task Time Blank on Delay = 
IF( CONTAINS( 
        FILTER(ALL(Workflow), [BI_SO_NBR] = EARLIER([BI_SO_NBR] && [SEQ_NUM] = EARLIER([SEQ_NUM]) )),
        Workflow[BI_WORK_EVENT_CD2],
        "DELAY" ),
    BLANK(),
    [04.Task Time] ) 

[GunnerJ · Accepted solution]

@Cmcmahan I worked with the code you provided and with a bit of syntax correction I arrived at the needed solution! Thank you so much for your help!

Task Time Blank on Delay =

IF( CONTAINS(

FILTER(ALL(Workflow), [BI_SO_NBR] = EARLIER([BI_SO_NBR]) && Workflow[BI_WRKFLW_TASK_SEQ_NBR] = EARLIER(Workflow[BI_WRKFLW_TASK_SEQ_NBR]) ),

Workflow[BI_WORK_EVENT_CD2],

"DELAY" ),

BLANK(),

[04.Task Time] )

[GunnerJ]

@Cmcmahan In the picture up top I have some data filtered down to one service order number and an individual sequence number. When adding in the new column I'd expect it to all be blank for the data present. However, all of the data shown in the picture just goes away so I can't scroll at all unfortuantly.