Using multiple criteria to build calculated table

[Clint]

Hello,

I am trying to build a calculated table (based on my Tasks table) that returns tasks based on two criteria:
1. The task is 100% complete OR

2. The task finish date is in the past <Today()

The following DAX works  but only returns things that are complete and in the past:

Tasks Closed or Overdue =

CALCULATETABLE('Tasks',

FILTER(

'tasks',Tasks[Task Percent Completed]=100),

FILTER(

'Tasks','Tasks'[Task Finish Date]<TODAY())

)

when I try to use OR, I get an error about multiple columns being referenced

Tasks Closed or Overdue =

CALCULATETABLE('Tasks',

or(

'tasks',Tasks[Task Percent Completed]=100),

'Tasks','Tasks'[Task Finish Date]<TODAY())

What's confusing about the DAX is, I have a table that only shows Tasks that have a finish date between Today+30 and today+60

Tasks starting between 30-60 = CALCULATETABLE('tasks',filter('tasks',Tasks[Task Finish Date]>TODAY()+30),FILTER('tasks','Tasks'[Task Finish Date]<TODAY()+60))

and this returns the correct results.  Not sure why this same expression is not working for the Tasks Complete or Overdue?

[Ashish_Mathur]

Hi,

Does this work?

Tasks Closed or Overdue = CALCULATETABLE('Tasks',FILTER('tasks',Tasks[Task Percent Completed]=100||'Tasks'[Task Finish Date]<TODAY()))

[Clint]

Hi @Ashish_Mathur ,

No, it  generates the error "A function 'Filter' has been used in a true/false expression that is used as a table filter expression.  This is not allowed."

[Clint]

I was able to work around the Filter function error by using the following DAX:

Tasks Closed or Overdue =

VAR Tasksoverdue =

FILTER('Tasks',Tasks[Task Finish Date]<TODAY())

return

CALCULATE('tasks','Tasks'[Task Percent Completed]=100)

||

Tasksoverdue

The problem is this returns the error "The expression refers to multiple columns.  Multiple columns cannot be converted to a scalar value".

[Ashish_Mathur]

Hi,

If you are OK with returning an actual Table/matrix visual on the canvas (instead of a virtual table), then I can help.

[Anonymous]

@CLS  i had similar requirement and I ended up creating a view in the database itself after multiple failed attempts using SUMMERIZETABLE/CALCUALTE/SUMMRIZE.

[Clint · Accepted solution]

Turns out it was a lot simpler than I was making it initially.  It was just a matter of me getting the syntax right:

Tasks Overdue or Closed =

FILTER('tasks',

'Tasks'[Task Finish Date]<TODAY()

||

'Tasks'[Task Percent Completed]=100

)