Forum Discussion
Select the correct record from a table
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.
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.
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.