Power Bi filtering with multi lookup columns

[Someguy154]

Hello everyone,

I'm trying to set up a Power Bi with data from a SharePoint site. I'm using multiple slicers and tables in order to filter and display only the data that I want. By default, it looks like the attached picture. My problem is the Which / Hvilke and Why / Hvorfor tables. They are connected to the other lists with a multi lookup column, and I cannot make Power Bi connect them to my other tables correctly. 

More specifically, I have a list called Consents. In this list, you select a number of Objects, which is a multi lookup to the Data Objects list. I have taken the Data Objects and Purpose from this list and put them in 2 tables underneath the one I have my consents in, and I want to be able to filter them. So when I select a Person in Who / Hvem, I want the tables to show the consent, and the Objects in the multi lookup column.

I know that multi lookups aren't supported in Power Bi (very unfortunate), but sometimes it works partially, as seen in the second picture. The data in this is correct, it's just missing a few Objects. If anyone knows if there's a workaround way I can get these 2 tables to filter correctly, either through SharePoint or PowerBi, then I'd appreciate the help. I can provide more details or screenshots as needed.

[v-yulgu-msft]

Hi @Someguy154,

That you mentioned about "multi lookup columns", "list" and "Objects" are all supported items in SharePoint site, however, they are not supported in Power BI. To establish relationships between these tables, you should first expand data to convert table to supported structure in desktop.

So, please show us the final table views, including table structure and sample data rows, and illustrate your requirement with examples to help us better understand.

Regards,

Yuliana Gu

[Someguy154]

Sure, no problem. I hope I understood you correctly, otherwise just tell me what else you need.

My requirement is simply that the Which and Why tables react properly when you filter the tables with the slicers on the left. If I select a person, I want to see all the Data Objects, shown in the Which table, that are related to that person. Suppose we click on a person in the Person slicer, I want the Which table to show only the objects we have on that person. The objects are in a multi lookup column in SharePoint.

When you say that I should expand data to convert tables to the supported structure, what does that mean?

The way the site is structured, we have a main Consensts list, with lookups to Systems, Data Subjects and Data Objects. Since the Systems and Data Subjects lookups are regular Lookups, I get the PersonID and SystemID values that I can use to connect them with the ID of the Consents. But the Data Objects don't have any such values, so I'm just using the IDs of both lists directly.