topic Re: Dax date issue in DAX Commands and Tips

Dax date issue

Anonymous — Tue, 01 Dec 2020 15:43:37 GMT

Hi,

I have my date table [Date] linked with a 1 to many relationship to with Resource Annual [Date].

The simple dax below works to calculate the first date in the series as the Production Start Year.

Production Start Year = MIN( 'Resource Annuals'[Date] )

Could somebody please tell me why when I use my date table year as a slicer it does not filter the matrix but instead returns the same year for each row of my matrix?

Thanks

Re: Dax date issue

mahoneypat — Wed, 02 Dec 2020 00:52:28 GMT

Make sure both columns in the relationship are Date type, and not DateTime. It is not enough to change the format; it needs to be changed in the query editor.

Regards,

Pat

Re: Dax date issue

Anonymous — Wed, 02 Dec 2020 18:39:52 GMT

Thanks for the response.

Both of my columns are formatted as date only.

The issue seems to be that once I slice by year using my date table the Resource Annual table is filtered to that year. This then become the new min date that is returned.

I presume I need to make use of ALLSELECTED but can't figure out the pattern to slice by year and retain the original Production Start Year.

Re: Dax date issue

mahoneypat — Thu, 03 Dec 2020 01:00:21 GMT

Your first post indicated that filtering wasn't happening and your last one suggest too much filtering is happening. In any case, ALLSELECTED will not remove filters coming from slicers outside your matrix. You need to use ALL instead. For example,

Production Year = CALCULATE(MIN('Resource Annuals'[Date]), ALL('Resource Annuals'))

Regards,

Pat