Losing values using one sided columns

[Tanaka]

Hello,

I have two tables like this.
Table A

TYPE	PRODUCT	VALUE A	DATE	KEY
TYPE 1	PRODUCT A	10	01/10/2021	(TYPE+PRODUCT+DATE)
TYPE 2	PRODUCT A	20	01/10/2021	(TYPE+PRODUCT+DATE)
TYPE 3	PRODUCT B	30	01/10/2021	(TYPE+PRODUCT+DATE)

Table B

TYPE	PRODUCT	STYLE	VALUE B	DATE	KEY
TYPE 1	PRODUCT A	STYLE 1	500	01/10/2021	(TYPE+PRODUCT+DATE)
TYPE 2	PRODUCT A	STYLE 1	500	01/10/2021	(TYPE+PRODUCT+DATE)
TYPE 3	PRODUCT B	STYLE 2	1000	01/10/2021	(TYPE+PRODUCT+DATE)

I connect them by the Key column making a one-to-many relation.
In a matrix i have Date as line values, VALUE A and VALUE B as values, when filtering by product i have the max value in the total line, e.g. filtering PRODUCT A, i get 30 in VALUE A Total, and 1000 in VALUE B Total, but...

When filtering this matrix by STYLE, VALUE A lose values, and VALUE B keeps the Total Value.

I tried use ALLEXCEPT in order to calculate VALUE A. Tried to merge tables. 

I know the error occurs because some STYLEs doesnt have VALUE A, so thats why i lose some values.

Any ideas?

Regards.

[AlexisOlson · Accepted solution]

Working with tables related like this is bound to be challenging. I'd recommend taking a step back and trying to set up a cleaner star schema model with dimension tables set up so that you don't have composite keys for relationships.

[Tanaka]

I have these 2 tables, and created a FactTable, but still using composite keys to link them.

When filtering by a column that exists in both tables i got these values.

When filtering by a single sided column i got these

I read the article that you shared, but can't imagine a better way to solve this.

[AlexisOlson]

Ideally, you'd probably want separate dimension tables for Type, Product, and Date. It's hard to make good recommendations without seeing a sample .pbix file though.

[Tanaka]

Well, thank you anyway.

I'll leave this in stand by, maybe in the near future i try again.