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.
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.
Solved! Go to 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.
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.
Well, thank you anyway.
I'll leave this in stand by, maybe in the near future i try again.
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |