Forum Discussion
Losing values using one sided columns
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.
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.
4 Replies
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.
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.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.
