Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SuddenClarity
Helper I
Helper I

Matrix is displaying every combination between two fields despite properly established relationships

I have a fact table with sales figures, a customer table and a product table. And they are set up using the classic star schema where the fact table contains the date, sales, customer ID and product ID. And the dimension tables contain their respective IDs and names. I also created a calculated calendar table which has a relationship with the sales table via the date columns. 

 

I created a simple measure to calculate the YTD sales, something like:

 

 

Sales YTD = CALCULATE (
                SUM ('Sales'[Sales]),
                DATESYTD ('Date'[Date])
              )

 

 

 

After that I created a matrix to view the YTD sales for each customer and the products they purchased. In the rows I put the customer at the top and then products at the bottom, and I put the [Sales YTD] measure in the values. 

 

What you would expect to see, is that under each customer only the products that they have purchased would be displayed. However the problem I have is that under each customer EVERY product is being displayed under them. Lets say in the products table I have 100 distinct products and customer A only purchased 5 different products. This matrix is showing all 100 products under Customer A where 95 show $0 and the 5 that he actually purchased show the correct sales amount. And it does this for many of the customers, however a handful of customers correctly display only the products they've purchased (not sure if that gives you a hint as to what my problem is)

 

I have checked the tables, the relationships, the product and customer IDs and there doesn't seem to be anything wrong with my data model. Other reports I have created in the past with this same data model doesn't have this issue. I don't know if this helps in any way, but the data from the sales model is a direct query from SQL Server but the product and customer table is simply excel data that I copied and pasted via the "Enter data" feature in PowerBI desktop. 

 

Any help or suggestions would be seriously appreciated!

 

1 ACCEPTED SOLUTION

@SuddenClarity , Make sure there is no +0 or handling blank or any addition of constant value in your measure

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

I solved this problem by changing the filtering direction from single to both. (The one that we change in relationship view)

HoangHugo
Solution Specialist
Solution Specialist

Hi, right click on field Product check if "Show item with no data" off

Capture 15.PNG

Thanks for the response. The "Show items with no data" option is already off so that doesn't seem to be the issue 😕

@SuddenClarity , Make sure there is no +0 or handling blank or any addition of constant value in your measure

Could you elaborate what you exactly mean and why this should help?

Great catch, it looks like it was caused by my conditional statement to handle the blank values.

Hi

I am having a similar issue.

Are you able to describe how the suggestion "Make sure there is no +0 or handling blank or any addition of constant value in your measure" worked for you?
Can you share how you fixed it?

Many thanks

Could you elaborate what you did to solve it? Having the same issue

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors