Creating a Unique Key field to accommodate data in a lookup table which changes over time

[Quenril]

Hello all,

Any help much appreciated please!

I'm building my first data model and have hit a snag. I've got a star schema with my fact tables at the bottom and my lookup tables at the top. My 'Consultant lookup' table contains the name of every Consultant and their Division. I need the data in my visualisations to be able to drill down to both Division and Consultant name levels of detail (both Primary keys).

Recently a staff member has moved Divisions. I don't want to overwrite the Division in the Consultant lookup table as historic information can't be lost. I need previous activity to show against his previous department.

In my Consultant lookup table, if I add an extra field  'Consultant Key' as a Primary key for each unique Consultant + Division combination, could I then use a formula to pull that unique 'Consultant Key' identifier into each of my fact tables?  Please could someone help with me the language? For each fact table I would need a conditional column which looks at the Consultant lookup table and matches both the Consultant name and the 'Effective from date' and then returns the 'Consultant key' field.

I realise this is not best practice as it duplicates data, however as a new user, I think it will be simpler for me than having a more complex lookup table with data that changes over time, and it would only require one lookup column per fact table.

Thank you very much

Becky  

[Quenril · Accepted solution]

Hello v-jiewu-msft,

Thank you so much for your response and sorry for the delay in reverting. This is a project I'm trying to fit in around my primary job... Since I originally posted my question my thinking has moved on slightly... rather than create a Consultant key comprised of Consultant Name and Division Name, I've instead spoken with our Finance team and now have unique Department codes (which I'm using as Consultant keys) from them which I've plugged into my Consultant lookup table.

Please could you kindly help me with creating a calculated column in each of my fact tables, which returns the Consultant key from the Consultant lookup table?  I've tried the formula you proposed above, adjusting it to my column names:

Department Code = LOOKUPVALUE(

    'Consultant lookup'[Department code],

    'Consultant lookup'[Consultant Name], 'Client Meetings'[Lead Consultant],

    'Consultant lookup'[Effective from date], 'Client Meetings'[Date of Meeting]

)

However I'm uncertain whether I have used the correct entries for the portion of the formula in bold. For these I've referenced the column in my Fact table (Client Meetings) and the name of the columns which include the Consultant name and Date to cross-reference to the Consultant lookup table.

Please can you help?

Thank you

[Anonymous]

Hi @Quenril ,

If I understand correctly, the issue is that you want to create a unique key for each consultant. Please try the following methods and check if they can solve your problem:

1.Create a Consultant Key in the table.

Consultant Key = [Consultant Name] & "-" & [Division]

2.Create a relationship between the key and the corresponding key in each of the fact tables.

3.You can create a calculated column in each fact table

4.Using a DAX formula with the lookupvalue function to match both the consultant name and the effective from date.

Consultant Key = LOOKUPVALUE(
         'Consultant lookup'[Consultant Key],
         'Consultant lookup'[Consultant Name], [Consultant Name in Fact Table],
         'Consultant lookup'[Effective from date], [Effective from date in Fact Table]
     )

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format or screenshot to make a deep troubleshooting? It would be helpful to find out the solution.

Looking forward to your reply.

Best Regards