Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kman42
Helper III
Helper III

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!

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.