matrix - Duplicate key measure columns appearing in a matrix

Question

The requirement: My users want a slicer which displays the dimensions of a data table they can then select from and have the dimensions display in a corresponding matrix visual along with two measures, Sales TY and Sales LY.

Progress so far. I’ve achieved this by taking the table, selecting the column which indexes the table and unpivoting the rest of the columns. Next I place a slicer on the report and attach it to the unpivoted table’s Attributes field (now renamed as ‘Column’). Finally, I add a matrix visual to the report and add to it the unpivoted table fields Column, Index and Value fields. Now when I click on the slicer, whichever dimensions I select appear in the matrix.

The problem: However, when adding the measures previously created (Sales TY and Sales LY) to the matrix, each measure is repeated after every column selected for display, whereas I only want the measures to display once.

Does anyone know if this possible? I’ve tried a number of approaches but nothing seems to work. I’m a new user so apologies if this is an FAQ I’ve yet to come across or I’m doing something glaringly obviously wrong. As I say I’m only just starting out in DAX and Power BI. I’m looking pretty hard at table functions and the Switch statement with the hope of calculating the solution somehow but haven’t managed it yet. Any advice, steer or comment greatly appreciated, thanks.

标签： matrixduplicatesmeasure