Using multiple filters in a calculate

I am trying to combine two filters. I search the forum and I am correct the code below should work:

AmountOverdueSurveys = 
  CALCULATE(
    DISTINCTCOUNT('project-surveys'[Id]); 
    FILTER('project-surveys'; 'project-surveys'[completed_at] <= [MaxDate] && 'project-surveys'[completed_at] >= [MinDate]); 
    FILTER('project-surveys'; 'project-surveys'[status] = "overdue")
  )

The problem is that the filter for status alone is work. And the filter for between dates also. But when I combine them they don't work anymore.

I also tried the following:

AmountOverdueSurveys = 
  CALCULATE(
    DISTINCTCOUNT('project-surveys'[Id]); 
         FILTER('project-surveys'; 
                'project-surveys'[completed_at] <= [MaxDate] 
                 && 'project-surveys'[completed_at] >= [MinDate] 
                 && 'project-surveys'[status] = "overdue"); 
  )

But that didn't work either.

What am I missing here? (and yes I am new to Power BI 😉 )

Hi LazyAdmin

You were almost there. Try this:

AmountOverdueSurveys =
CALCULATE(
    DISTINCTCOUNT( 'project-surveys'[Id] );
    FILTER(
        'project-surveys';
        'project-surveys'[completed_at] <= [MaxDate]
            && 'project-surveys'[completed_at] >= [MinDate]
            && 'project-surveys'[status] = "overdue"
    )
)

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

3 Replies

MartynRamsden
Solution Sage
6 years ago
Hi LazyAdmin

You were almost there. Try this:
AmountOverdueSurveys = CALCULATE( DISTINCTCOUNT( 'project-surveys'[Id] ); FILTER( 'project-surveys'; 'project-surveys'[completed_at] <= [MaxDate] && 'project-surveys'[completed_at] >= [MinDate] && 'project-surveys'[status] = "overdue" ) )

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.
- LazyAdmin
  New Member
  6 years ago
  It worked, was the same solution as my second formula. The mistake I made in the date filter is that an overdue record is never completed, so it doesn't have a complete_at date (only a scheduled date).
  - MartynRamsden
    Solution Sage
    6 years ago
    Hi LazyAdmin
    
    Your 2nd DAX expression was almost there, you just had an extra semicolon on the last line of your filter expression which would have caused an error.
    
    Best regards,
    Martyn
    
    If I answered your question, please help others by accepting it as a solution.

Forum Discussion

Using multiple filters in a calculate