cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Creating a new table in Power BI

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
Highlighted
Community Support
Community Support

Re: Creating a new table in Power BI

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors