Select the correct record from a table

[fbj1973]

Hi all,

I am woking with two datasets, the Highway Rail Accident (HRA) and the Grade Crossing Inventory (GCI) Historical. Both datasets are available online at data.transportation.gov in the railroads section. Both datasets contain a column named gradecrossignid (HRA) and crossingid (GCI) respectively. The value identifies each grade crossing in the United States. The GCI contains all the crossings in the US with its revisions and updates over time since 1970. Essentially, if a crossing gets a new set of gates, a new record is created and the GCI historical gets updated. The column revisiondate in the GCI dataset tells you when the record was changed. I want to analyse accidents at grade crossing, but I also want to know the condition of the crossing at the time of the accident. I specifically wanting to select a accident record from the HRA, and pull the properties of the crossing at the time of the incident. I cannot use the gradecrossingid or crossingid for a relationshiop beacuse it would be a "many to many" and does not help (one crossing could have many accident records and many revisions). I think I need to create a measure but I do not know how for this particular case. 
Thanks for any help out there!

[lbendlin]

Read about SCD2 or "slow changing dimensions"  and the methods to handle them.

Pretty interesting data

What happened in 2009?  Major safety initiative?

Here is one possible data model

and here's an example of some accident dates. Not sure how "Change in Data" is supposed to help.

Looks like the History file is not enough. You also seem to need the Current file for the latest revision.

[fbj1973]

Hi @lbendlin , thanks for the first look at this. I downloaed and opened your dataset, but I see that one relationship is missing. 

To answer your question about what happened in 2009. In 2008 Congress enacted the Rail Safety Improvement Act which among other things mandated that all railroads that hosted passenger service and carried hazmat implement Positive Train Control (PTC). This was in reponse to a head-on collision between a Union Pacific freight train and a Metrolink communter train in Chadsworth, CA in 2008 where the conductor of the UP train was texting from his phone and did not see a stop signal. 

Regarding the grade crossing accident data, it flattened since 2009 because the key contributors to the previous downward effect (installation of gates and bells at passive crossings, adding reflective markers on railcars and locomotives, the creation of Operation Livesaver, among others) started to wear out. 

Reagarding my issue, I see that date relationships appear to be key. However, when I create a table with Date from the calendar table, crossing ID from the accident table, and some other info from that same table, I have no issue. But when I try to add a characteristic of the grade crossing from the historical file (assume the accident happened in 2015), say, number of gates, or number of highway lanes, that is where I get an error. PowerBI says that a relationship is missing, and I cannot figure out which one. 

[lbendlin]

Thank you for the background info, that always helps.

Relationships in DAX can only operate based on the "equal"  priniciple.  What you need would be a relationship based on "largest prior or equal" - that is not something you can do in DAX.  Hence the revisions table cannot be linked into the calendar (unlike I was showing in the original screenshot), it can only be used in LOOKUPVALUE or TREATAS fashion.

I was thinking anyway that it would be better to show all prior revisions of a crossing's data for a selected incident, not just the latest revision.