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 sho...
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.

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi again. OK so I have progressed a bit, but might need to take a step back;

I have two tables, where this is the columns in question:

 

"fact_Orders"

Order ID | Loaded Date

 

"dim_Trailers"

Order ID | Trailer Number

 

The tables are linked on "Order ID", many-to-one single direction.

Each new order gets assigned a trailer number and loading date from the order system.

 

I need a visual (table) "Last Used" looking back on for example last 30 days (visual filter), listing each "Trailer Number" which have been used last 30 days, and the LATEST "Loaded Date" for that trailer. So each trailer should appear only once in my "Last Used" table. In addition, a calculation of how many days since "Last Loaded" (from todays date). So output:

 

Visual: Table, "Last Used"

Trailer ID | Last Loaded | Days Since Last Loaded

 

I have managed (with tips above) to filter out only the latest loading dates, but have not been able to calculate "days since last loaded". 

 

Any help out there? Thanks in advance!