Data explodes when adding a measure to the table

DAX DAX Calculations
#1

Hi All,

Please see below simplified PBIX showing the issue I am facing.

Exploding Table.pbix (20.2 KB)

I have two tables, with the example extracting [ID] and [REG] from the Parent table and [ID] and [TEXT] coming from the child table. As you can see from the below, the relationship is identifying the two [ID] columns and joining them as expected.
image

However, When I go to add a measure to the table, the data explodes showing repeating data from the parent table, as shown below:-

image
image222×624 4.72 KB

Any help in preventing this from happening is appreciated.

Many Thanks,

James

#2

Bumping this post for more visibility from our experts and users.

#3

It sounds like something’s gone wrong with the relationship between your two tables in Power BI. When you try to add a measure to your table, the data is exploding and showing repeating data from the parent table.

There are a few things you can try:

  1. Check the relationship between the tables: Make sure you’ve set up the relationship correctly and selected the right columns as keys.
  2. Look for duplicate values: Check both tables for duplicate values in the columns used as keys. These could be causing the data to explode.
  3. Check your measure: Make sure your measure is aggregating the data correctly. If it’s not, that could be causing the problem.
  4. Check your data model: Look at your data model and check for any circular references or other issues that could be causing the problem.
  5. Try a different visualization: If the problem persists, try using a different visualization to see if the issue is with the table or the visualization itself.
#4

Hello @JamesDavies

Did the response above from @AlissonLima help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

#5

Hi,

Apologies for the delay.

Thank you for your solutions, however, it’s not solved my issue.

The only solution I have been able to find is either not including any measures within my Table visual or selecting bi-directional relationship between the two tables which will affect the performance.

If you imagine my example being 1 customer is buying a vehicle and in doing so, has traded in multiple vehicles, that is similar to what I am trying to achieve. Hopefully this makes sense!

Many Thanks,

James

#6

Hi @JamesDavies,

This is a bit of data joining fun! Your Units measure is on the child table looking up, so it’s value will only ever be 1 because each Child row only has one parent. However, the data “explodes” because in the context of the measure, it is being repeated for every row in the Child table every time you ask for it to be calculated. Note how the sequence of the numbers in the second ID column repeat over and over.

Another question is what exact count you are trying to get per line item (or per parent?). I have 3 options for you.

Step 1 to fix this is to create a measure table. Use “Enter Table” to create a new manual table, name it something obvious like “Measures”, then save it (with no data entered).

Step 2 is to create measures for the counts you would like under that new Measures table. This depends on context at the time the measure is calculated. I created 2 measures to satisfy the 3 options I mentioned, using your attached PBIX (I have September 2022 Desktop): CountRows_Child and Count_ParentsChildren.

The first measure, CountRows_Child, is shown below along with the example tables for the new measures. This measure will count all the rows in the Child table for the given context. The first table on the left is the one you included. I removed Units and added CountRows_Child. Because the context is every line item under every parent, the count is always 1.

In the middle table, I removed the Child columns which made the context each row in the Parent table. You can see that the same exact measure now returns the total number of Child records for each Parent ID.

image
image918×285 11.6 KB

The second measure, Count_ParentsChildren, assumes you want to know for every Child record how many total children the Parent ID has. This requires changing the context for each calculation of the measure to extract the Parent ID value, then calculate the count of Child records for that ID regardless of the current context.

image

In the table on the right, you can see that for each Child line item displayed, the correct count of the Parent’s children is displayed.

My changes are in the attached PBIX, based on your original.

John
Exploding Table.pbix (21.9 KB)

#7

Hi @JamesDavies,

Just checking if you had the time to check out @jpratt solution above.

Please let us know if this helped you solve your inquiry or if you need further assistance.

Thank you!

#8

Hi,

Thank you for the time you’ve spent on my issue, I think this is the answer to the problem and makes complete sense.

If I have any further issues, I will reply to this thread.

Thank you for your time.

James