Forum Discussion

LazyAdmin's avatar
LazyAdmin
New Member
6 years ago
Solved

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

DAX
Filters
  • MartynRamsden's avatar
    MartynRamsden
    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.

3 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Icon for Solution Sage rankSolution 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's avatar
      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's avatar
        MartynRamsden
        Icon for Solution Sage rankSolution 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.