Creating a new table in Power BI

[BradRose]

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

[v-shex-msft · Accepted solution]

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