Forum Discussion

jdubs's avatar
jdubs
Icon for Helper V rankHelper V
6 years ago

Filter based on two conditions

I'm trying to return values in a Matrix where Orders associated with an Account that were invoiced last week but also show Open orders (i.e. not invoiced). I am able to create this Matrix but once I ...
Need Help
DataZoe's avatar
DataZoe
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

jdubs The filter pane should be able to take two conditions on the same field, but you won't be able to select a relative date such as last 7 days.

 

You can alteratively create a measure or calculated column to "flag" rows with the multiple conditions met and then filter the matrix with it instead.

 

 

 

for a calculated column:

OpenOrLast7DaysOrders=if(or(isblank([Invoice Date]),datediff(today(),[Invoice Date],DAY)>=-7),1,0)

 

for measure (if the table is large this is a more performant option but you will need to have Invoice Date in the table):

OpenOrLast7DaysOrders=if(or(isblank(min(Orders[Invoice Date])),datediff(today(),min(Orders[Invoice Date]),DAY)>=-7),1,0)

 

jdubs's avatar
jdubs
Icon for Helper V rankHelper V
6 years ago

When I use this formula: OpenOrLast7DaysOrders=if(or(isblank([Invoice Date]),datediff(today(),[Invoice Date],DAY)>=-7),1,0)

 

I get an error telling me that a "single value cannot be determined."