Data model

[Anonymous]

Hi

Can you assist with my data model as the data tables are aggregates as opposed to having a primary key. What would my model look like to be able to see per item cost versus revenue targets for items/genders.

Table 1

Date	Item	Gender - size	Cost
1/1/2024	Hat	Male - 32	12.22
1/1/2024	Shoe	Female - 6	10.98
1/1/2024	Socks	Male – one size	3.44
2/1/2024	Hat	Female - 28	8.66
2/1/2024	Shoe	Female-4	4.66

Table 2

Date	Item	Gender	Revenue target
1/1/2024	Hat	Male	22.00
1/1/2024	Hat	Female	5.00
1/1/2024	Shoe	Male	12.00
1/1/2024	Shoe	Female	3.00
1/1/2024	Socks	Male	10.00

Table 3 - Date table

[Anonymous]

Thanks yeah i did try that but unfortunately it wont form the relationship between cost and revenue target as it said that the item column in table 1 contains duplicates and that's not allowed for a 1:many or many:1 relationship

[Uzi2019]

Hi @Anonymous 

You can connect your Date with both Table1 and Table 2
make sure your relationship from Date  to Table 1 is 1 ->many (Both direction)

and relationship from Date  to Table 2 is 1 ->many (Both direction)


put your slicer on top from Date table filed.

Or you can have bridge table where your unique values of gender/ item will be store . connect your both the tables ( 1&2) with bridge table and your issue will be resolved.

Note: You have to decide the flow of your filter data. It should be from cost table or revenue table. which is the primary or main table. based on that you can take unique value in bridge table and decide the flow like single direction and both direction.
Advice: Never create relationship with many to many direction avoid using that . it creates ambiguity. you can reolved this by using bridge table.

I hope above solution would help you to make your model working.

[Irwan]

hello @Anonymous 

please check if this what you need.

1. Create relationship of your 'Date' table to 'Table 1' and 'Table 2'.

- 'Date' to 'Table 1' -> Date

- 'Table 1' to 'Table 2' -> Item

2. Plot you Date and Item into slicer visual. While Cost and Revenue in card visual.

Hope this will help you.

Thank you.