Nesting AND and OR in FILTER

[kman42]

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!

[v-deddai1-msft · Accepted solution]

Hi @kman42 ,

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

[jdbuchanan71]

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"
    )
)