Duplicate Measures appearing on a matrix

The requirement:
My users want a slicer which displays the dimensions of a data table (organisation) they can then select from and have the dimensions display in a corresponding matrix 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 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.

Hi @JoeC,
What’s your model like? Are you able to post a screenshot?
Thanks
Fiona

Hi Fiona, thanks for getting back to me. Its company sensitive data which I can’t share but what I will do is create a blank document with dummy data and just the slicer and the matrix with the two measures so you can see the problem. It might be an hour or two but I’ll do that this morning (UK 10:22). Many thanks,
Joe

Hi @JoeC
I had also had an issue with duplicate lines appearing in my graph. I later realised that I had a many to many relationship between two dimensions so I created a bridge table and modified as well the cross filter direction so that solved my issue.

Post your model and we’ll see if your issue is similar to mine

Cheers
Fiona

Hi @Fiona,
Here’s the pbix and the dummy data.
Thanks for taking a look. Don’t worry about the summation problems I’ve solved that in my real report. This is just to demo the duplicating measure columns issue.
Thanks again,

Joe

Matrix Problem.pbix (245.4 KB) DummyOrgsAndCalendar.xlsx (733.8 KB)

Hi @JoeC,
What is the process you are trying to model? Is it tracking the total sales of each sales team in a particular organisation?

The problem with you current model is that the filtering from Organization to the sales is not in the correct order :
data_model

The filtering is going “upwards” instead of downloads to the Sales table as your Date dimension. If you change the relationship filter to by-directional you’ll force the filtering to flow downwards to the Sales table. Dimensions example (Date, Organisation )must flow downwards to correctly filter the Fact tables and thus give you correct calculations.

But in saying that, I suggest these modifications to improve your data model

  • Extract proper dimensions:
    Organization data - You should have a distinct list of Organisation Code, Name, Group, Region etc. Redo this table and you should have a one to many relationship between the organisation and sales table thus having a downward flow from the Organisation down to the sales table

Attached is a proposed model - mind you I dont know much about your data requirements but I hope this will give you an idea of how easily you can analyse your data once a good data model is in place
Matrix Problem_V2.pbix (372.1 KB)

Hi Fiona,
Thanks for looking at it. I realize the filtering was in the wrong direction, I think that’s a consequence of my rushing a quick demo. I tried to create it the other way round and the relationship manager wouldn’t allow it. However, this is a side issue. In the real document it is in the right direction and the correct data is returned on the rows.

The problem is that still doesn’t stop measures appearing again each time I select I select a new dimension from the slicer. Correcting the filtering doesn’t stop the duplication problem. I’m not ungrateful though, thanks for your time in considering it.

Much appreciated,

Joe :slight_smile:

Hi @JoeC
When you have repeating measure data it means that there is no relationship setup between the dimensions (Lookup) tables and the Fact (in your case “Sales”) table - or the relationship is not setup correctly. So the issue is on the data model and not on your Dax calculation.

Ensure that you have the mappings correct in both the dimensions and fact tables. I sometimes make erros when dragging and dropping the columns when creating the relationships.

Cheers
Fiona

Hi @Fiona,
Brilliant, many thanks. I just couldn’t work out what aspect or approach I was just not getting. I’ll work away at it and see if that fixes it. I’ll post the model once I crack it. Thanks again. Joe