Many-to-many relationship to make the visual work even if it actually is a one-to-many

[UltraNoob]

Hello everyone,

I'm no data, it or BI expert. But i need to deliver some reports to my customers.

This particular reports retrieve OData from iserver365 views (an Enterprise Architecture tool). Relationships are a bit complicated and i have tables listing elements as well as relationship tables linking two elements.

Problem: customers noticed some elements are not showing in table visuals (even if not explicitly  filtered out), especially when one or more linked attribute are not present (e.g. if the app-100 does not have a "data owne"r i can't see other values, even if not dependent or linke to "data owner") but i have the "show empty elements" ticked.

elements are present when looking for them in tables in the data section, also what's showing inside the visual tables changes based on the column order and i do not understand why is that.

i tried rebuilding from scratch, using dax's lookup values instead of merging queries, trying to avoid many-to-many relationships.

but the issue is:

i now have only tables with one-to-many relationships but the table visual will show me an error unless i change a relationship (that is a one-to-many) into a many to many. even if it formally isn't. and i think that's what's causing problems with filters and stuff.

can someone help? i'll try to post a couple of screenshots

you can see that originally i had a many-to-many relationship between "Application-peripheral" and "rel-subfunction..."

got rid of that by creating another table (i needed that cause, originally, in the subfunction table values multiplied because of the "split on rows" for a field with multiple values separated by commas).

I now have 2 one-to-many relationships instead of 1 many-to-many.

But, as you can see, my visual table does not work unless I switch to a many-to-many with double-sided filters

screens

original model (problems with showing items, probablly filtering?)correct relationship with new table, but visual not workingbroken visual, after new table and relationshipsedit relationship as a many-to-many (even if it's not) and double-sided filters stops the visual error (no guarantee the visual is showing everything it should)visual shows no error after changing the re to many-to-many.

[v-yueyunzh-msft · Accepted solution]

Hi , @UltraNoob 

According to your description, when you use the "many-to-may" relationship , the visual it works, but the table logic is "one-to-many". Right?

First, i recommand you to use "one-to-many" relationship between tables if the table logic does it.

For the reason for this, you can kown and check this point:

(1)For the fields we place on the visual, we first need to make it clear that a one-end table can filter a multi-ended table, but a multi-ended table can't filter a one-end table by default unless we turn on a bidirectional relationship.

(2)On our visuals, we try to put data from a table on our dimension field as much as possible.

(3)For your case, you can try to delete some fields and directly find which field affects the final result and causes the error, or you can view its detailed error information for troubleshooting.

If the above methods do not meet your needs, you can delete the sensitive information and provide your .pbix file to us so that we can help you better.

Best Regards,

Aniya Zhang

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

[UltraNoob]

Hi there @v-yueyunzh-msft ,

thank you for replying that fast and sorry for my late answer.

you're right: the table logic is one-to-many and i tried with that relationship. the problem is the visual does not work.

I also think I've got what's breaking it:

• putting any element from the table on the upper left "Rel - Organization ..." and any element from the table "ITSD Stream Tab" together, on the same visual, breaks the visual table, unless i use the many-to-many relationship  and it does not make sense to me
  • tried to work around that using 2 separate visual tables  (cause it still works if i put all the info i need, except for the organization table info, in one table and the applications + organization info into another) but i still have a problem on the first table (probably on the second too, did not try yet): i have a field (the "itsd stream", from "itsd stream tab") that has various values but, when the value is "", it's replaced by "NA" so no blanks. No matter how i order the column (ascendant, descendant, ...) table, it does not show me empty fields BUT if i filter by app name, layer or even Stream, suddenly rows with empty "ITSD Sream" value appear and, at this point, i feel like the table  is unreliable because i am not sure if anything is working with filters or not or if any table field is making it show only certain things or things that should not be there at all.

I hope i was clear, english is not my first language and i really am a total noob

I really am in a predicament and don't know how to give a working version to my customer

Thank you anyway and have a good day!

[v-yueyunzh-msft]

Hi , @UltraNoob 

According to your description, when you filter , it appears "NA".

For this situation, you should check your table in Power Query that if this column have "NA" data .

You can check your table in it .You can also replace the "NA" to null.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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