Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter a table based on date

Hello everyone!

 

In a table, I have a list over trailer ID numbers. Next column is "loaded date", showing when the trailers was used. But I want to filter the table so only the LATEST entry is show. As circled, for trailer DBN885 I only want to see the row with "05.01.2020" date. The two columns comes from two different (linked) tables, if that matters. Thanks in advance for any quick tips!

 

 

Regards,

Robert O

Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi Anonymous 

     

    Click on your table visualization, then, in the Visualizations panel, click on your Date column and select 'Latest'.

    That should summarize your table and only show the max loaded date for each trailer.

     

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

4 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi Anonymous 

     

    Click on your table visualization, then, in the Visualizations panel, click on your Date column and select 'Latest'.

    That should summarize your table and only show the max loaded date for each trailer.

     

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MartynRamsden 

       

      Thanks, it sort of did the trick. Initially it just picked up the latest date in my filter, but when adjusting the cross filter direction between the tables from "single" to "both" it worked.

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Hi Anonymous 

         

        Be very cautious when enabling bi-directional relationships between tables in your data model as you can very quickly end up with incorrect results! You can read more about this here: www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/ 


        As an alternative solution, you could create a measure instead, which enables the bi-directional relationship only for the life of the calculation.

        Something like this:

        Last Loaded Date = 
CALCULATE ( 
    MAX ('Calendar'[Date] ),
    CROSSFILTER ( 'Calendar'[Date], Trailer[Trailer Number], BOTH )
)

         

        You'll obviosuly have to replace the column references in the CROSSFILTER function with the correct columns references from either side of the relationship in your model.

         

        Best regards,

        Martyn

         

        If I answered your question, please help others by accepting it as a solution.