Forum Discussion
Matrix using left outer join to display all data from one table and linked data from another.
Hello, new boy here.
I hope this makes sense:
I`m trying to create a matrix in my report which shows all item product groups down the side and all salesmen across the top (I`ve done this). I want the values to be taken from a sales table using a date range from a slicer but when I bring the values in to the matrix the rows and columns shrink so that only the salesmen and product groups are shown where there is a sales value but I want all groups and salemen to be shown whether there is sales data or not.
The two tables are linked by a product group/salemen field I`ve created in both tables, I`ve tried to create the relationship to be the group/salesmen table at the top and the sales table at the bottom but when I save this as a one to many cardinality it flips around to be a many to one which I`m assuming is the issue. As I understand it by default the relationship is a left outer join with the top table being the left.
4 Replies
- 79775722Regular Visitor
I have a similar need, but I want the matrix table to show both the parent side on the left and the many side columns on the right.
I want to apply filter on the many side to show certain records that meets the filter criteria. If not met, I still want to show the parent on the left side. Is this possible on the Matrix table visual side without changing the data model by applying filter at the PowerQuery ETL side? Thanks.
- hmqcRegular Visitor
I have the same requirement, I need a Matrix Visualization with the principal table on the side and secundary table in the columns.