Forum Discussion

JimJim's avatar
JimJim
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Override filter context - multiple date tables

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 ...
Need Help
  • jpessoa8's avatar
    jpessoa8
    2 years ago

    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

jpessoa8's avatar
jpessoa8
Icon for Continued Contributor rankContinued Contributor
2 years ago

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's avatar
JimJim
Icon for Responsive Resident rankResponsive Resident
2 years ago

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.