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
BradRose
Helper I
Helper I

Creating a new table in Power BI

 

 

Here the data we have currently in our Fact Table, I can create a unique key on Customer / Business

 

Fact Table

Customer

Business

Date

Dollar Sales

FactKey

Kroger

Beverage

5/1/2017

$400

KrogerBeverage

Kroger

Meals

5/1/2017

$600

KrogerMeals

Kroger

Snacking

5/1/2017

$200

KrogerSnacking

Kroger

Pizza

5/1/2017

$300

KrogerPizza

Kroger

Ice Cream

5/1/2017

$750

KrogerIce Cream

Walmart

Beverage

5/1/2017

$2,000

WalmartBeverage

Walmart

Meals

5/1/2017

$3,000

WalmartMeals

Walmart

Snacking

5/1/2017

$1,000

WalmartSnacking

Walmart

Pizza

5/1/2017

$1,500

WalmartPizza

Walmart

Ice Cream

5/1/2017

$3,750

WalmartIce Cream

 

 

We have an employee table, however there are multiple employees that are responsible for the same Customer / Business combination.

 

 

Employee Table

Employee

Customer

Business

EmployeeKey (Option1)

EmployeeKey (Option2)

1

Kroger

Beverage

KrogerBeverage

1KrogerBeverage

1

Kroger

Meals

KrogerMeals

1KrogerMeals

1

Kroger

Snacking

KrogerSnacking

1KrogerSnacking

1

Kroger

Pizza

KrogerPizza

1KrogerPizza

1

Kroger

Ice Cream

KrogerIce Cream

1KrogerIce Cream

1

Walmart

Beverage

WalmartBeverage

1WalmartBeverage

1

Walmart

Meals

WalmartMeals

1WalmartMeals

1

Walmart

Snacking

WalmartSnacking

1WalmartSnacking

1

Walmart

Pizza

WalmartPizza

1WalmartPizza

1

Walmart

Ice Cream

WalmartIce Cream

1WalmartIce Cream

2

Walmart

Meals

WalmartMeals

2WalmartMeals

2

Walmart

Snacking

WalmartSnacking

2WalmartSnacking

 

I can create a key EmployeeKey (Option1) in the Employee Table to link to the fact table, however the key is not unique and thus I can’t link it to my fact table.  I can create EmployeeKey (Option2) which is unique however the Employee ID is not in the fact table thus I can’t link it.

 

Any thoughts.

 

Thanks

 

 

Brad

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @BradRose,

 

In my opinion, I think you can create a new table with unique customer and business value, then use the new table to link two tables.

 

Steps:

1. Create a calculated column to merge customer and business.

Customer Business = [Customer]&" "&[Business]


2. Use customer business to create a new table.

 

Customer Business = VALUES('Fact'[Customer Business])

 

3. Create relationships between fact, employee and above table.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @BradRose,

 

In my opinion, I think you can create a new table with unique customer and business value, then use the new table to link two tables.

 

Steps:

1. Create a calculated column to merge customer and business.

Customer Business = [Customer]&" "&[Business]


2. Use customer business to create a new table.

 

Customer Business = VALUES('Fact'[Customer Business])

 

3. Create relationships between fact, employee and above table.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.