Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dax date issue

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  

DAX

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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