Can I simplify this expression?

Manish_Jain11 · 05-11-2021 05:53 AM

I have to fill a column in Production Table with value thats based on the value from a column “Process_Status” in Prod_Process table. The Production is referred in the Prod_Process table in column Production_IDs column which is a list.

I have written the expression and its working fine but somewhere I think it can be simplified to reduce the sync time. Can someone suggest ?

IFS( 
        
     IN("Not Processed",SELECT(Prod_Process[Process_Status],IN([Production_ID],         [Production_IDs]))),
     "In Process",
     
    IN("Part Complete",SELECT(Prod_Process[Process_Status],IN([Production_ID],         [Production_IDs]))),
     "In Process",  
     
    OR(
    
    NOT(IN("Not Processed",SELECT(Prod_Process[Process_Status],IN([Production_ID],         [Production_IDs])))),
    NOT(IN("Part complete",SELECT(Prod_Process[Process_Status],IN([Production_ID],         [Production_IDs]))))    
    ),
    "Complete"   
  )

Steve

Reformatted for my clarity:

IFS( 
  IN(
    "Not Processed",
    SELECT(
      Prod_Process[Process_Status],
      IN(
        [Production_ID],
        [Production_IDs]
      )
    )
  ),
    "In Process",
  IN(
    "Part Complete",
    SELECT(
      Prod_Process[Process_Status],
      IN(
        [Production_ID],
        [Production_IDs]
      )
    )
  ),
    "In Process",  
  OR(
    NOT(
      IN(
        "Not Processed",
        SELECT(
          Prod_Process[Process_Status],
          IN(
            [Production_ID],
            [Production_IDs]
          )
        )
      )
    ),
    NOT(
      IN(
        "Part complete",
        SELECT(
          Prod_Process[Process_Status],
          IN(
            [Production_ID],
            [Production_IDs]
          )
        )
      )
    )
  ),
    "Complete"   
)

The third condition is entirely redundant: it just rechecks the first two. If the third is reached, it means the first two have already failed, so there’s no need to recheck them.

IFS( 
  IN(
    "Not Processed",
    SELECT(
      Prod_Process[Process_Status],
      IN(
        [Production_ID],
        [Production_IDs]
      )
    )
  ),
    "In Process",
  IN(
    "Part Complete",
    SELECT(
      Prod_Process[Process_Status],
      IN(
        [Production_ID],
        [Production_IDs]
      )
    )
  ),
    "In Process",  
  TRUE,
    "Complete"   
)

And the two remaining SELECT() expressions can be combined into just a single table scan, since they both produce the same result:

IF( 
  ISNOTBLANK(
    FILTER(
      "Prod_Process",
      AND(
        IN(
          [Production_ID],
          [Production_IDs]
        ),
        IN(
          [Process_Status],
          LIST(
            "Not Processed",
            "Part Complete"
          )
        )
      )
    )
  ),
  "In Process",
  "Complete"   
)

Aurelien

Thanks @Steve for the clarity

@Manish_Jain1
Maybe you can merge the first two terms, as they are supposed to give the same output ?

 IN(
    "Not Processed",
    SELECT(
      Prod_Process[Process_Status],
      IN(
        [Production_ID],
        [Production_IDs]
      )
    )
  ),
    "In Process",
  IN(
    "Part Complete",
    SELECT(
      Prod_Process[Process_Status],
      IN(
        [Production_ID],
        [Production_IDs]
      )
    )
  ),
    "In Process",

to turn into:

ISNOTBLANK(
   INTERSECT(
      LIST("Not Processed", "Part Complete"),
      SELECT(...)
   )
), "In Process"

Manish_Jain11

Thanks Mate… Will work on it and correct the expression .

Manish_Jain11

Thanks Steve,

Yes I was trying to figure this out as to what I should give as condition there when all the other conditions fail.

Let me correct the expression .

Manish_Jain11

@Steve
Dont you think , the list parameter should come first in this one as thats the value to search in [process_status] list in the prod_process table. I might be wrong though, not quiet sure but just a doubt that the first parameter in the IN should be the value to search I believe.

Steve

IN(a, b) answers the question, “is a in b?”

Manish_Jain11

Hi Steve,

The above formula doesnt seem working. I think I have not been able to explain properly. Its showing complete for all the entries even if the value in the Process_Status is one of the values in the list.

IF(
  
    ISNOTBLANK(
              Filter(Prod_Process,
              AND(
                  IN(Production_ID],[Production_IDs]),
                  IN([Process_Status],List("To Send", "Part Complete"))
                 )
              )
              ),
              
   "In Process",
   "Complete"  
  )

Another thing , I wanted to clear is there are 2 tables we are comparing in this code and for both we are writing the column names directly without the table name like Table[Column Name] and in the code above inside the first condition , the first parameter is of Table PRODUCTION and in the second condition, the first parameter is of Table PROD_PROCESS. Can that create a problem ?

Marc_Dillon

Definitely.

Inside of a FILTER/SELECT, you are in the context of the records of the Table that you are filtering/selecting on. In this case, the Prod_Process. To reference values from columns from the record that your expression is originating from (presumably, from a record on the Production table), you dereference with [_THISROW].

I’d assume you should be doing this (changes wrapped in double asterisks):

Manish_Jain1:

IF(
  
    ISNOTBLANK(
              Filter(Prod_Process,
              AND(
                  IN(**[_THISROW].[**Production_ID],[Production_IDs]),
                  IN([Process_Status],List("To Send", "Part Complete"))
                 )
              )
              ),
              
   "In Process",
   "Complete"  
  )

Manish_Jain11

Thanks Mate. This worked.