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

     

     

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.