Forum Discussion
Helper IIITable stops working when columns are in a specific order
Super UserHi 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.
twofingertyperThank 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.
- twofingertyperI 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'