topic Re: Compairing a date measure to a date column in the same dimension table in DAX Commands and Tips

Compairing a date measure to a date column in the same dimension table

arc_gts — Wed, 29 Dec 2021 15:52:44 GMT

I'm trying to create a filter to determine the inclusion or exclusion of records in a dimension table based on whether the Threshold Date for each record is less than the calculated Last Date measure. There's probably a much more intelligent way to do this, but I'm kind of stumbling my way around here and am not very good with DAX.

The threshold date column is native to the imported Excel spreadsheet that I'm using as a dimension table; the measure is based on a calendar dimension table imported from a data warehouse, and I'm selecting date ranges using a date slicer in the report. I've tried calculating the Last Date two different ways:

1) Using CALCULATE and LASTDATE: CALCULATE(LASTDATE('Calendar'[Date (EDW)]), ALLSELECTED('Calendar'[Date (EDW)]))

2) Using MAXX: MAXX(ALLSELECTED('Calendar'),'Calendar'[Date (EDW)])

Both work fine as measures. If I try to convert them into dimensions (or create them as columns instead of measures), they'll ignore the slicer and just return the last date from my entire calendar table. Similarly, if I try to create an IF statement returning a one or zero based on whether the Threshold Date falls before the Last Date, the formula returns a value of one for all records in the dimension table with a Threshold Date that falls before the last date in my calendar table.

Is there a way to compare my Last Date and Threshold Date that will still apply the slicer to the Last Date?

I can't create an active relationship between the calendar dimension table and the dimension table containing Threshold Dates because the calendar dimension table already joins to two fact tables which then join to the dimension table containing Threshold Dates.

Re: Compairing a date measure to a date column in the same dimension table

speedramps — Wed, 29 Dec 2021 15:58:50 GMT

Sorry I dont understand you.

May I suggest rather than you try explain you rsolution (which does not work and you want us to fix).

That you simply explain the problem more clearly and with an exmaple, then I am sure we can all help you with this date filter issue.

Thanks

Re: Compairing a date measure to a date column in the same dimension table

smpa01 — Wed, 29 Dec 2021 16:08:18 GMT

arc_gts provide sample data and expected output

Re: Compairing a date measure to a date column in the same dimension table

arc_gts — Wed, 29 Dec 2021 18:21:39 GMT

Goal: add an indicator that I can use as a filter to show or hide rows in a table based on a date in the table vs. a date in a slicer (no direct relationship between date slicer table and dimension table)

Expected outcome: rows in the dimension table with a greater date than the maximum selected slicer date value should have a value of zero, while rows in the dimension table with a value less than / equal to the maximum selected slicer date value should have a value of 1

Actual outcome: rows in the dimension table that have date value that exists in the calendar table have a value of 1, ignoring the selected range in the slicer; rows in the dimension table that have a date value that doesn't exist in the calendar table (greater than the max date value) have a value of zero

Please see below. It looks like I'm unable to upload a PBIX file.

Re: Compairing a date measure to a date column in the same dimension table

speedramps — Wed, 29 Dec 2021 19:15:01 GMT

Thanks.

You cant upload PBIX to the forum.

You need to save it on Onedrop or Dropbox with public view access and then share the link on the forum.

Re: Compairing a date measure to a date column in the same dimension table

speedramps — Wed, 29 Dec 2021 19:38:03 GMT

Try this Click here to download PBIX

Re: Compairing a date measure to a date column in the same dimension table

arc_gts — Thu, 30 Dec 2021 01:09:48 GMT

PBIX file link

Re: Compairing a date measure to a date column in the same dimension table

smpa01 — Thu, 30 Dec 2021 01:25:02 GMT

arc_gts you can write this two measures

_isThresholdLesserThanLastSelected = if(MAX(Category[Threshold Date])<=[Category last date],0,1) _isThresholdGTThanLastSelected = if(MAX(Category[Threshold Date])>[Category last date],0,1)