Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Karen1015
Frequent Visitor

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 different customer lists for credit cards, saving accounts, and loans. But the customer ID may exist in more than in a customer list. For example, if a customer uses a credit card and a saving account in the same bank, the customer exits in the credit card table and the saving account table.

 

How to make the customers from different departments relative to one another?

The only way as below I come up with is combining all customer lists from different departments to get a unique customer list, and then use it as a primary key table to build connections with one another.

 

credit card table

Customer IDTypes
A123456....
A123789...

 

saving account table

Customer IDAmount
A123456...
A224455...

 

Loan account table

Customer IDAmount
A123147...
A123789...

 

To get a primary table, merge the credit card table, the saving account table, and the loan account table.

Customer ID
A123456
A123789
A224455
A123147

 

Is any other solution to solve it? Can it solve by using Dax? Because there will be millions of data from different departments. Merging all of them to get a unique customer ID costs a lot of time. Thank you.

 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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] )
    )
)

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

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] )
    )
)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors