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
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
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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.