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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.