Forum Discussion

rjs2's avatar
rjs2
Resolver I
6 years ago
Solved

Max sequence number filtered

The data I am working has a parent sequence number and a child sequence number.  I want to be able to return the max sequence number filtered on a date.

 

I have tried many diffent ways to accomplish this with no success.

 

I have been able to count the child sequence number with a filter on the parent sequence number and the date):

Test = calculate(counta('TABLE'[CHILD_SEQ]),FILTER(ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE])))
 
This returns a count of child sequence number that are on the same date.   I now want to return the maximum child sequence number on the row that has the maximum child sequence number and nothing for any row that is not a maximum, based on the filter above.
 
Everytime I try to use an if statement i get: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value
 
Thanks!
 
RJ
  • rjs2's avatar
    rjs2
    6 years ago

    Took some time, but I solved it.  It was easier to stop into it with two columns

     

    First new column:

    Max on Day = CALCULATE(MAX('Vetting Query'[CHILD_SEQ]),ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE]))

     

    This gave me the max sequence number for the child sequence per day on every row related to that parrent seq.

     

    Second new column:

    Number Processed = if('TABLE[Max on Day]='TABLE[CHILD_SEQ],'Vetting Query'[CHILD_SEQ],"")

     

    The Number Processed will only have the Max CHILD_SEQ on the row of the Max sequence number and a blank if its not the max sequence number.

     

2 Replies

  • rjs2's avatar
    rjs2
    Resolver I
    6 years ago

    I tried this as well:

     

    Test2 = VAR SEQ= CALCULATE(MAX('Vetting Query'[CHILD_SEQ]),ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE]))
    Return
    SEQ

     

    This is the error I get:

     

    The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

    • rjs2's avatar
      rjs2
      Resolver I
      6 years ago

      Took some time, but I solved it.  It was easier to stop into it with two columns

       

      First new column:

      Max on Day = CALCULATE(MAX('Vetting Query'[CHILD_SEQ]),ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE]))

       

      This gave me the max sequence number for the child sequence per day on every row related to that parrent seq.

       

      Second new column:

      Number Processed = if('TABLE[Max on Day]='TABLE[CHILD_SEQ],'Vetting Query'[CHILD_SEQ],"")

       

      The Number Processed will only have the Max CHILD_SEQ on the row of the Max sequence number and a blank if its not the max sequence number.