CALCULATE + Multiple Filters

[learning_dax]

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 time between candidates interview (Interview Date) to when they start (Start Date). I am sure you can do this in a calculated column and it works; However, I love using Zebra BI's Cards which show multiple measures in one view which compares the values & shows % and # increase/decrease. 

Additionally, I need to add multiple filters in these measures.

-I have a separate Date table linked to "Start Date"
- Specific date range (last year December 2021)

-All columns values but certain ones excluded (for instance all "County" except for "San Fran" && "Apple"

I've also attached an example of what I am trying to solve with my calculation but it is not coming out correct & I have tried multiple versions of it including CALCULATE(FILTER)) etc. 

[OwenAuger]

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]

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 · Accepted solution]

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]

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

[ValtteriN]

Hi,

AVERAGEX needs table reference as its firs input so isntead of Table[column] use Table. For the CALCULATE use MAX(Table[Country]) instead of Table[Country].

[learning_dax]

Hi ValterriN, 
What does the MAX do in this case and what does it change?