Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |