Forum Discussion

Clint's avatar
Clint
Helper V
6 years ago
Solved

Using multiple criteria to build calculated table

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?

Need Help
  • Clint's avatar
    Clint
    6 years ago

    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

    )

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Does this work?

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

    • Clint's avatar
      Clint
      Helper V
      6 years ago

      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's avatar
        Clint
        Helper V
        6 years ago

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