Table stops working when columns are in a specific order

[twofingertyper]

Hi all, 

This is a bit of a vague query that I will try to flesh out with a demonstration if I can find a report where I can get the same thing to happen that I am able to share. 

Essentially, I linked a table to my fact table so I could include some of it's fields in a summary table, I found that the data would not work properly (with only some of the data from the fact table populating) when I had the two columns from the additional table in the centre of the table visual; however, when I moved them to the end, everything populated as normal - which would suggest to me that it was not a relationship issue. 

However, the relationship is a single direction many-to-many due to the way the report was initially set up, one table provides certification details (so a product ID may be certified for numerous things, e.g. safety, heights that will then each have their own row and date attached to when that happened) and the main table is a product summary, but certain products can have more than one target market, and again, this may be something that I look to fix with a bridging table to try and match unique values and more to more standard relationships.

So, I can fix it, I am aware it's not best practice - but why does Power BI stop mid-way through a detail if the fields from the supplementary certifications table are in the middle of the table, but when they're at the end of the table all works fine? 

It seems like it should either be entirely broken, or work fine - regardless of the column positions?

[Anonymous]

Hi, @twofingertyper 

Have you already solved the current problem? If yes, you can share your solution here that was helpful/shared, and then mark your reply as a solution so that others in the community can find some ideas to solve similar problems.
Your cooperation will work with us to promote the development of the community! Thank you again!

Best Regards

Jianpeng Li

[MFelix]

Hi @twofingertyper ,

If you are using a many to many relationship, this as a big impact on your model and visualizations performance, the fact that you get the value stop in middle or at the end depends on the aggregations and the way ou have the several columns because there can be a change in the way the Select statement is done and the result is an error or a correct calculation.

The two table visualizations below are exactly the same but they were built in a different order:

If you check the code for both of them you get the following:

You can see that the code is basically the same but the order of the columns is different. Of course  this is just a very small example but you understand the context of this. 

This implication is even more complex when adding relationships with many to many and filters on top of your model.

I would suggest to create a bridge table between you both table to have a 1 to many relationship between those tables.

Check this video were you have a complete explanation of this type of setup:

https://www.youtube.com/watch?v=wRSJ6TYjEu0

It's well worth the hour.

[twofingertyper]

I made the bridging table today - it did not work! For some reason whenever using fields from both tables (main table and certificates) in the same table it would result in an error. This was apparently (after some discussions with ChatGPT) because Power BI is unable to decide how to propagate the filter correctly because both tables are filtered by the Bridge Table, but through different relationships. ChatGPT then suggested using measures, etc which seemed overkill for a few columns in a table (not even a fancy table) and much easier to merge them into a single source. So I opted to just merge the columns that I would be using as a workaround which is fine given the size of data involved, but I would like to solve the problem properly when I have more time as a 'learning experience'

[twofingertyper]

Thank you - I will watch that video tomorrow; I am fairly familar with issues with many-to-many (even with directional filtering in place) but have picked up this report from someone else and clearly it was working, so they didn't overly worry.

I'll add in a bridge table tomorrow, this was something I was intending to do anyway, it was just that I had never come across this specific experience (where data populates or doesn't depending on column order) before.