Forum Discussion

learning_dax's avatar
learning_dax
Icon for Helper II rankHelper II
4 years ago
Solved

CALCULATE + Multiple Filters

Hi all,    I am comparing thetwo Date columns in the below screenshot (this is not the real dataset of course so there are more dates dating back to years prior). I am comparing YoY averages of the...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Yes, you sure can 🙂

     

    You can change the AVERAGEX expression as follows to check that both dates are nonblank (using Wait_Time Base as an example):

    Wait_Time Base =
AVERAGEX (
    'Table',
    VAR StartDate = 'Table'[Start Date]
    VAR InterviewDate = 'Table'[Interview Date]
    RETURN
        IF (
            AND ( NOT ISBLANK ( StartDate ), NOT ISBLANK ( InterviewDate ) ),
            INT ( StartDate - InterviewDate )
            -- Otherwise return blank, which will be ignored by AVERAGEX
        )
)

    Regards,

    Owen

     

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
4 years ago

Hi learning_dax 

 

A few of things to mention here:

  • A DAX variable's value takes a fixed value once defined. You can't define a measure using a variable (you would have to create a separate measure for that).
  • Your AVERAGEX expression is almost correct. The error is that the first argument of AVERAGEX must be a table (not a column reference).
  • For a difference between two dates, I would recommend subtracting and converting to integer, rather than the DATEDIFF function.
  • You can use the IN operator for the Count filter.
  • It's a bit unusual to apply a static date range filter in a measure, but assume that you require it.

Given those points, I would recommend writing two measures

  • Wait_Time Base which is similar to the variable in your expression above
  • Wait_Time which applies the additional filters
Wait_Time Base =
AVERAGEX (
    'Table',
    INT ( 'Table'[Start Date] - 'Table'[Interview Date] )
)
Wait_Time =
CALCULATE (
    [Wait_Time Base],
    NOT 'Table'[County] IN { "Apple", "Lemon" },
    DATESBETWEEN (
        DimDate[Date],
        DATE ( 2021, 12, 01 ),
        DATE ( 2021, 12, 31 )
    )
)

 

You could combine these into one measure also:

Wait_Time =
CALCULATE (
	AVERAGEX (
		'Table',
		INT ( 'Table'[Start Date] - 'Table'[Interview Date] )
	),
    NOT 'Table'[County] IN { "Apple", "Lemon" },
    DATESBETWEEN (
        DimDate[Date],
        DATE ( 2021, 12, 01 ),
        DATE ( 2021, 12, 31 )
    )
)

 Regards,

Owen

  • learning_dax's avatar
    learning_dax
    Icon for Helper II rankHelper II
    4 years ago

    Hi OwenAuger & thank you for the response, 

    This almost worked. However, I did not notice for some of the values in "Interview Date" it is blank. Thus, I am receiving a number that is not accurate because it is still subtracting "Start Date" minus the blank "Interview Date" and giving me very high numbers as the difference. Any way to only include the calculation only for when the two are occupied with data? 

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      4 years ago

      Yes, you sure can 🙂

       

      You can change the AVERAGEX expression as follows to check that both dates are nonblank (using Wait_Time Base as an example):

      Wait_Time Base =
AVERAGEX (
    'Table',
    VAR StartDate = 'Table'[Start Date]
    VAR InterviewDate = 'Table'[Interview Date]
    RETURN
        IF (
            AND ( NOT ISBLANK ( StartDate ), NOT ISBLANK ( InterviewDate ) ),
            INT ( StartDate - InterviewDate )
            -- Otherwise return blank, which will be ignored by AVERAGEX
        )
)

      Regards,

      Owen

       

       

      • learning_dax's avatar
        learning_dax
        Icon for Helper II rankHelper II
        4 years ago

        Thanks Owen. This seemed to work. Appreciate the help.