Best practices for unique values tables with changing historical data

[jsbourni]

Hi,

I need help on the best way to deal with a problem of unique values over time.

I'm working with student data (university level). We follow registrations applying filters on programs, units (groupings of programs), and faculty/department. This is a kind of hierarchy where programs are in units, and units are in departments. I "had" these 3 tables with unique values connected with each other until I realized that in order to get unique values in programs I had to apply a filter on active programs only, therefore getting rid of all past data.

The problem is that programs may have changed units over time creating multiple entries in the program tables and duplicates. I re-created the tables, but I can't connect them (say, programs to units) because of duplicates.

My solutions:

1. Get unique values for each table but not connecting them, so no conflict.
   1. The drawbacks of that is that sometimes I only have the program in a table. I would not be able to filter through higher levels (units, departments)
2. Create a concatenated key (program, unit, department)
   1. Again, not useful in the most probable case where the data is not part of a table
3. Create intermediate tables: for instance, unique values of programs, a summarize of programs and units, unique values of units, a summarize of units and departments, unique values of departments.

What would be your take on that?

Thanks

[DataNinja777 · Accepted solution]

Hi @jsbourni ,

In order to deal with the student data which has multiple changes in units for a given program, one of the solutions is to maintain the change log of the units which a program belongs over time like below. 

Program	Units	Date	End
A	AA	1/1/2024	12/31/2024
A	AB	1/1/2025	12/31/2025
B	BB	1/1/2024	12/31/2024
C	BA	1/1/2025	12/31/2025

In order to do this analysis, you need to create a calendar table and have it as a disconnected table in your data model.  

Units over time =
CONCATENATEX (
    DISTINCT ( 'Change log' ),
    IF (
        'Change log'[Start] <= MAX ( 'Calendar'[Date] )
            && 'Change log'[End] >= MAX ( 'Calendar'[Date] ),
        'Change log'[Units],
        BLANK ()
    )
)

Your challenge described sounds similar to other common business scenarios where employees change departments over time in employee table and sales reps responsible for a customer change over time in customer table and in those cases, we can use the change log table like above, and bring in the time dimension in the analysis.  

The important thing to note is that such time dimension duration analysis only works when the calendar table is set as a disconnected table in the data model. 

I may be able to provide more details if I can understand your specific requirements better.  

Best regards,

[jsbourni]

Hi @DataNinja777,

Thanks for your quick reply. In fact, I almost volontarily omit the option of a time-related table because of a more general problem of logs in our "old" system (it is migrating into a newer structure that would make things easier). But you are totally right. How would you connect such table? Each program occurrence in, say, the registration table would have to pass through that unrelated table to evaluate the period and choose the right combination, and I would be able to connect units to a unique table?

Thanks again

JS