Show data from two tables related by many-to-many

[njkbr]

Hi!

I am new to PBI and I have a hard time understanding if I can achieve what I want with PBI.

I have a set of data and know it is not the best structure for PBI as I do not have facts and dimensions.

I have several issues with ambiguity, but here is another issue with several many-to-many connections:

I can use one of Terms in my output, but I get an error if I try to show both Terms at once.

I found some similar issues on this Forum and they have solutions. However, the proposed solutions that I understand are not applicable in my case. And those that may be the solution for me - I do not understand fully. 

As I did not find an example with the same structure - I would be very thankful for some advice and help!

[JGroothedde]

Hi @njkbr ,

Have you tried setting the relationships to many-to-many and both directions instead of single?

Honestly i think it might be better to take this opportunity to clean up your model and move to more of a star schema instead of going for many-to-many bi-directional relationships, since they will give you unexpected results in a lot of calculations. 

[njkbr]

Hi @JGroothedde,

I have tried that, but it did not help:

Was that the only thing you did?

Based on your result, can it be the case that each of your 'main_id' has only one correspondence in both 'term1_main_link' and 'term2_main_link'?

And thanks for your advice, I also have several cases of ambiguity in connections like this one:

 And I have more with a bigger scale (5-6 tables make a closed round). I suppose it will be faster and easier to create a star schema, but for now, the task is to understand what can be done with the current schema.

Appreciate your help and advice!

[JGroothedde]

Ah that complicates the issue. Is it possible for you to share your model so we can take a look in the model itself? 

[njkbr]

Hi @JGroothedde, I got an approval to share it like this:

The whole model:

Here you can see the case with the Terms I asked originally:

And several ambiguities:

I understand PBI is not designed to work with relational schema, but we want to make sure there are no workarounds as it might be easier than migration and maintenance of a star schema.

Would appreciate your expertise, thanks in advance!

[JGroothedde]

Thanks for sharing @njkbr ! I will take some time later today to check this out and see if i can find a solution for you! 

[njkbr]

Hey @JGroothedde,

Hope you're doing well! Just checking in to see if you had a chance to check the model. No worries if you're swamped, just thought I'd ask. Thanks a bunch!

[Daniel29195]

@njkbr 

can you show the model in your power bi , how it looks like ? 

best regards

[njkbr]

Hi @Daniel29195, sure.

Model view tab:

And Report view tab:

When I show only one of the term names - everything is fine.  When I want to show both - I get an error because Term1 and Term2 tables are not connected.

As I understand - possible solution will be to create a new table with all possible combinations, but it is very inefficient.

Here I could see an issue that might be relevant, but I lack understanding how to build a View if I drop a connection.

Also here are some other topics I found most related, if it can be of any help for you:

Unable to show data from related tables at the same time - solution is not applicable for me with link tables;

Returning value from inactive related table - this topic might be helpful, but I lack understanding the solution. I suppose writing some custom queries might solve the issue, in such case I will go and read more about measures, crossfilters and how to use all of that in PBI 🙂

Please, tell me if you need any additional info. And many thanks for such a quick response!