Creating a new table in Power BI

Question

&nbsp;&nbsp;Here the data we have currently in our Fact Table, I can create a unique key on Customer / Business&nbsp;Fact TableCustomerBusinessDateDollar SalesFactKeyKrogerBeverage5/1/2017$400KrogerBeverageKrogerMeals5/1/2017$600KrogerMealsKrogerSnacking5/1/2017$200KrogerSnackingKrogerPizza5/1/2017$300KrogerPizzaKrogerIce Cream5/1/2017$750KrogerIce CreamWalmartBeverage5/1/2017$2,000WalmartBeverageWalmartMeals5/1/2017$3,000WalmartMealsWalmartSnacking5/1/2017$1,000WalmartSnackingWalmartPizza5/1/2017$1,500WalmartPizzaWalmartIce Cream5/1/2017$3,750WalmartIce Cream&nbsp;&nbsp;We have an employee table, however there are multiple employees that are responsible for the same Customer / Business combination.&nbsp;&nbsp;Employee TableEmployeeCustomerBusinessEmployeeKey (Option1)EmployeeKey (Option2)1KrogerBeverageKrogerBeverage1KrogerBeverage1KrogerMealsKrogerMeals1KrogerMeals1KrogerSnackingKrogerSnacking1KrogerSnacking1KrogerPizzaKrogerPizza1KrogerPizza1KrogerIce CreamKrogerIce Cream1KrogerIce Cream1WalmartBeverageWalmartBeverage1WalmartBeverage1WalmartMealsWalmartMeals1WalmartMeals1WalmartSnackingWalmartSnacking1WalmartSnacking1WalmartPizzaWalmartPizza1WalmartPizza1WalmartIce CreamWalmartIce Cream1WalmartIce Cream2WalmartMealsWalmartMeals2WalmartMeals2WalmartSnackingWalmartSnacking2WalmartSnacking&nbsp;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.&nbsp; I can create EmployeeKey (Option2) which is unique however the Employee ID is not in the fact table thus I can’t link it.&nbsp;Any thoughts.&nbsp;Thanks&nbsp;&nbsp;Brad&nbsp;

anonymous · Accepted Answer

Hi BradRose,
&nbsp;
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.
&nbsp;
Steps:
1. Create a calculated column to merge customer and business.
Customer Business = [Customer]&amp;" "&amp;[Business]
2. Use customer business to create a new table.
&nbsp;
Customer Business = VALUES('Fact'[Customer Business])
&nbsp;
3. Create relationships between fact, employee and above table.
&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Creating a new table in Power BI

1 Reply

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating Date Tables

Creating a couple of tables

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

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