Power Bi - SharePoint List Multiple Value columns

[KnucklezZ]

Hi,

I'm just starting my journey with Power Bi, but I will try to explain it the best I can.

I have connected Power Bi to a SharePoint list to make some analysis from the data. I have a few columns in that list where you can have more than one value (one is a list with options, another is people). This has caused some probleme because it pulls through as a list and a table in Power Bi. Extracting the data isn't really an option because it then duplicates the rows, so I went around creating separate tables for these columns, and linking them to the main table using the unique ID.  

This works but only when I'm trying to visualise using one of the dim tables. If I want to make a visualisation which uses two of the dim tables, I get an error saying there is no relationship between the two, and the only relationship I can create is a many-to-many which I know is not a great one to be using.

I have attached a screenshot showing the model with the tables:

Does anyone have an idea what's the best approach to this?

Much appreciated.

[Anonymous]

Hi @KnucklezZ ,

You may consider modify the relationship like below:

Final output:

Best Regards,

Ada Wang

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

[KnucklezZ]

Thanks for the reply.

I have tried to replicate these steps, but I am getting a blank table. 😞

Can I also ask, what's the reasoning behind two of the tables being many-to-many and the middle table being many-to-1, also the filter in the middle one seems to only be one way.