Forum Discussion

Karen1015's avatar
Karen1015
Frequent Visitor
3 years ago
Solved

How to Practically Use a Primary key and Foreign Keys for Bank Data

I have a question. I want to know how to use a primary key and foreign keys practically.  A Bank has different departments, such as for credit cards, saving accounts, and for loans. So, it has diffe...
Data Protection
Need Help
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    That is the correct approach to use, to have one table containing the unique customers which then has relationships to all the other tables.

    There are different ways of achieving the unique list. There may be a table you can get from the source which already contains this information. If not then Power Query is probably the best place to do it. You can take a reference to each of your existing tables, delete all the columns except customer key, remove duplicate values, append all the queries together and then remove all duplicate values again. It would probably be worth doing some testing to see if removing the duplicates before doing the append improves performance or not.

    As a last resort you could do it in DAX by creating a table like 

    Unique Customers =
DISTINCT (
    UNION (
        DISTINCT ( 'Bank account'[Customer ID] ),
        DISTINCT ( 'Credit Card'[Customer ID] ),
        DISTINCT ( 'Loan'[Customer ID] )
    )
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

That is the correct approach to use, to have one table containing the unique customers which then has relationships to all the other tables.

There are different ways of achieving the unique list. There may be a table you can get from the source which already contains this information. If not then Power Query is probably the best place to do it. You can take a reference to each of your existing tables, delete all the columns except customer key, remove duplicate values, append all the queries together and then remove all duplicate values again. It would probably be worth doing some testing to see if removing the duplicates before doing the append improves performance or not.

As a last resort you could do it in DAX by creating a table like 

Unique Customers =
DISTINCT (
    UNION (
        DISTINCT ( 'Bank account'[Customer ID] ),
        DISTINCT ( 'Credit Card'[Customer ID] ),
        DISTINCT ( 'Loan'[Customer ID] )
    )
)