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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dax Measure showing non related data

Hello,

 

I'm struggling with PowerBI measures.

 

There are two tables where the Customer ID links them:

 

 

 

 

When adding a simple Measure it shows all the IDs/Names from Dim_Customer even if they are not related to the data:

 

 

Does someone have any idea why it is happening? How to solve it?

 

PBIX File: File Link

 

5 REPLIES 5
SebSchoon1
Post Patron
Post Patron

Hello,

 

may i suggest you to simply Change your relation from both Sides to one unique side?

 

SebSchoon1_0-1663054679684.png

 

Anonymous
Not applicable

Hi @Anonymous ,

 

I do not know what you are trying to achieve, but looking at your output it looks correct.

 

The CUST from Fact_sales matches the Cust ID from Dim_Customer. Since it found only 1 ID match i.e CUST ID=1, so in the table it shows the summation for ABC and other blanks as there is no match for their ID.

 

Thanks,

Tejaswi

Anonymous
Not applicable

Thank you for your quick support @Anonymous 

 

In the example above the Customer id for "Dona" is 3, but when I add the measure, it shows 1, even if the data is properly related in the data relationship.

 

Tables.png

 

The biggest issue is when it multiplies the number of rows. 

 

Example 2:

 

I need to calculate the sum(Sales)+1 to all customers and products:

 

If I use the measure = sum(Fact_Sales[Sales])+1. PowerBI shows data for non-related dimensions and multiplies the number of rows:

 

Table issue.PNG

 

My real model has more than 10 dimensions linked to one fact table with 6M rows. Every time I add similar measures, it multiplies the 6M rows from the fact table by each dimension size.

 

It's crazy. I can't find any forum explaining this issue as I can't understand what I'm doing wrong =(.

Anonymous
Not applicable

Hi @Anonymous ,

 

I dont know if this solutions works for you.

But give a shot

 

-In a query editor, I created a Index col for both these tables.

 

Capture9.PNG

 

-Deleted all the old relationships and created a new relationships by joining th Index somethign like this, as it gives me the unique number

Capture11.PNG

 

- Now in a table I get the output something like this:

Capture8.PNG

 

You can now create yout measure to sum the sales and see what you get.

 

Thanks,

Tejaswi

Hi @Anonymous ,

 

Did you try to create a calculated column to implement it ?

Column = Dim_Customer[Sales] + 1

3.PNG

Because of the relationship,  the Cust ID matches the entire data of the other table. 

If you still can't solve it, can you please post the sample data and the expected output?  

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.