fighting many to many with a bridge

[blndspt]

Hey guys,

I'm relatively new to Power BI.  I've built embedding and done alot of modeling, I'm still just not that good with visuals yet.  I have a very simple set of 3 tables that include a many to many relationship with a one to many hanging off the end.  I've setup the bridge correctly, but when I try creating a simple matrix visual from beginning to end, that last one to many table causes the dreaded 'fix this'.   I sincerely apologize in advance, but can someone tell me why this doesn't work?

As you can see, the psudo-hierarchy starts on the left with organizations.   There is a simple bridge to location2, in which case many orgs can have many locations and vice versa (hence the bridge and the many to many).  Now, I can make a simple matrix visual with the org name then the location name, and it works fine.  However, when I add the terminal name from the last table it no longer understands the relationship.  Shouldn't I be able to make a matrix visual with org name, then location name, then terminal name?

In general data modeling, this is not an uncommon scenario!  Does PowerBI only allow for traversal of 1 set of relationships when a bridge table is in the mix?

Thanks everyone.

Scott

[d_gosbell]

---

@blndspt wrote:

In general data modeling, this is not an uncommon scenario!  Does PowerBI only allow for traversal of 1 set of relationships when a bridge table is in the mix?

---

So the data engine can handle this ok, the issue here seems to be with the query generator in Power BI. The only work around I can see is to add a measure to the table visual.

If you create a simple count of terminals like the following and add it to your table visual it should "fix" this issue at the cost of having the extra count column.

Terminal Count = COUNTROWS(terminals2)

[blndspt]

Weird.  This seems like the worlds most simple many to many relationship.  It would seem that most m2m table relationships would have another table hanging off of one end or the other.  Works as expected in SQL Server.  That's incredibly disappointing. 

[d_gosbell]

If you hand write a DAX query against a model like this it works fine too. It looks to me like this is probably a bug in the way the table visual generates it's underlying query, the measure work around will just let you get something working now.