Forum Discussion

Quenril's avatar
Quenril
Icon for Resolver I rankResolver I
2 years ago
Solved

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

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...
Need Help
  • Quenril's avatar
    Quenril
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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