Override filter context - multiple date tables

[JimJim]

I have a fact table (quotes) and two date dimension tables (sent date and created date). We have two date tables because users self serve using the dataset and sometimes need to see quotes by either sent date or created date. All good so far.

A user has asked for a measure showing rolling 3 months of quotes, I created the measure like so:

Quote Count R3M =
var m = MAX('Date (Sent)'[YearMonthKey])
return
CALCULATE(quotes[Quote Count],
ALL('Date (Sent)'),
'Date (Sent)'[YearMonthKey] >= m-2 && 'Date (Sent)'[YearMonthKey] <= m)

The problem: After creating the measure I realised that the user may not always be filtering on sent date, he may filter created date which means the above measure won't work.

What is the best solution for dealing with this? I would like a single measure that will work regardless of what date the user uses to filter

Have also provided the link to a report with my problem multiple date tables.pbix

Edit: One possible solution (although I don't like it) would be to create two measures using each date table (one of them would always be blank and both would be hidden from end users. Then choose which measure to display with a helper measure. Such as:

Quote Count R3M master =

IF(measure1 > 0, measure1, measure2)

But I will wait on someone smarter than me to come along with a more elegant solution.

[jpessoa8 · Accepted solution]

Hi @JimJim,

You could validate directly in the measure which field the user is using with the function ISFILTERED.

VAR mSent = MAX('Date (Sent)'[YearMonthKey])
VAR mCreated = MAX('Date (Created)'[YearMonthKey])
RETURN
IF(
	ISFILTERED('Date (Sent)'),
	CALCULATE(
		[Quote Count],
		ALL('Date (Sent)'),
		'Date (Sent)'[YearMonthKey] >= mSent - 2 && 'Date (Sent)'[YearMonthKey] <= mSent
	),
	CALCULATE(
		[Quote Count],
		ALL('Date (Created)'),
		'Date (Created)'[YearMonthKey] >= mCreated - 2 && 'Date (Created)'[YearMonthKey] <= mCreated
	)
)

If the user is using a field from the "'Date (Sent)'" table the ISFILTERED('Date (Sent)') function will return TRUE and do the calculation with the logic for the 'Date (Sent)' table. 

If the user is using a field from the 'Date (Created)' it will use the other logic.

If the user is using both of them, it will calculate using the 'Date (Sent)' logic.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[JimJim]

@jpessoa8 , thank you sir, that would indeed work when the user has at least one filter active. The problem could arise when a user doesn't use it as a filter but instead used the data to slice the data, for example adding one of the dates to the columns on a matrix. But this has given me something to think about.

[jpessoa8]

Hi @JimJim ,

Although the name of the function might be misleading, the ISFILTERED takes in consideration both the usage of a table/column as a Filter/Slicer or as a Column in a matrix (because indirectly it's also applying a filter with that field)

Here is some documentation on that function https://dax.guide/isfiltered/ .

For additional help, please @ me in your reply!

Please consider marking it as a solution ✔️ or giving it a kudo 👍

Best regards,
Jorge Pessoa

[JimJim]

You are correct, I just tested it. Thank you for your help.