Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Nesting AND and OR in FILTER

I need to nest an AND and an OR clause in a FILTER, but I can't figure out how to do it.

 

 

Count of subcontracts in process = CALCULATE( COUNTROWS( subcontracts )
      , FILTER( VALUES( subcontracts[subcontract_begin_date] )
                ,  subcontracts[subcontract_begin_date]  <  max(CalendarAwardStartDate[date] )
      )
      , FILTER( values(subcontracts[execution_date])
                ,subcontracts[execution_date]>max(CalendarAwardStartDate[Date]) || (isblank(subcontracts[execution_date]) && subcontracts[status]="Fully executed")
      )
)

 

 

When I try this, the subcontracts[status] field gives me a red squiggly underline. Is this a context issue or something else?

 

Thanks!

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Please refer to the following measure:

    Count of subcontracts in process =
CALCULATE (
    COUNTROWS ( subcontracts ),
    FILTER (
        VALUES ( subcontracts[subcontract_begin_date] ),
        AND (
            subcontracts[subcontract_begin_date] < MAX ( CalendarAwardStartDate[date] ),
            OR (
                subcontracts[execution_date] > MAX ( CalendarAwardStartDate[Date] ),
                AND (
                    ISBLANK ( subcontracts[execution_date] ),
                    subcontracts[status] = "Fully executed"
                )
            )
        )
    )
)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    I think you might have just had a ) in the wrong spot, does this work for you?

    Count of subcontracts in process =
CALCULATE (
    COUNTROWS ( subcontracts ),
    FILTER (
        VALUES ( subcontracts[subcontract_begin_date] ),
        subcontracts[subcontract_begin_date] < MAX ( CalendarAwardStartDate[date] )
    ),
    FILTER (
        VALUES ( subcontracts[execution_date] ),
        ( subcontracts[execution_date] > MAX ( CalendarAwardStartDate[Date] ) || ISBLANK ( subcontracts[execution_date] ) )
        && subcontracts[status] = "Fully executed"
    )
)
  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Please refer to the following measure:

    Count of subcontracts in process =
CALCULATE (
    COUNTROWS ( subcontracts ),
    FILTER (
        VALUES ( subcontracts[subcontract_begin_date] ),
        AND (
            subcontracts[subcontract_begin_date] < MAX ( CalendarAwardStartDate[date] ),
            OR (
                subcontracts[execution_date] > MAX ( CalendarAwardStartDate[Date] ),
                AND (
                    ISBLANK ( subcontracts[execution_date] ),
                    subcontracts[status] = "Fully executed"
                )
            )
        )
    )
)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai