Element / List relationship bewteen tables (element contained in list) | Filtering visuals

[Anonymous]

Hello,

I have a problem concerning filters and relationships bewteen tables in Power BI. I have two tables. The first one represents links between two entities (sort of like sellers), if they are linked IRL, there is a row in my first table, containing information about them, and in one of the column, there is a list of IDs (representing "how" they are linked) which identify the sales they made together. So you have for instance : 

ID_seller_1 | ID_seller_2 | .... |     list_of_IDs

18783783    18783124          [176,289,209,...]

The second table represents the sales, each row has the id of the sale (same as found in the list of IDs shown earlier), and informations about them. 

In Power-BI, I have two table visual, one with columns from the first table, and the other one with columns from the second table. What I want is that, whenever the user selects a row in the first table (the one with the links), the reports filters the data on the second visual, to show only the sales with the IDs inside the list of the column "list_of_IDs" inside said list. 

I can't figure out how to create this kind of filter nor how a list-element relationship between tables would work in power BI. Is there a tool to do it, has anyone out there done something similar in Power BI ? 

I would greatly appreciate your help, thank you in advance

Max Dedieu

[V-pazhen-msft · Accepted solution]

@Anonymous 

I created some data:

Table1:

Table2:

Here are the steps you can follow：

1. Create measure.

Measure =

var _select=SELECTEDVALUE('Table1'[list_of_IDs])

return

IF(

_select=MAX('Table2'[list_of_IDs]),1,0)

2. Place Measure in Filter, select is =1, apply.

3. Result:

Select a list_of_IDs of Table1, Table2 will only display the corresponding data:

Does this match your expected result ?

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous I would recommend as a first step to split your list out in Power Query based upon the comma and then unpivot the columns.

[Anonymous]

@Greg_Deckler Thanks for the response ! I think I see how it could solve the problem, however, as I see it, it wouldn't help me see "all" elements whose IDs are on the list I'm selecting. I would only make them appear one-by-one. Am I missing something here ? 

Thank you again for your help

Max

[Greg_Deckler]

@Anonymous If you unpivot and then have a Both cross-filter relationship between your two tables based on sale id. You can create two visuals, one with your IDs from your sales table and then another with the seller id's from the table you showed. Click on the seller id in the second table visual and the first table visual will contain only the sale id's for that seller.

[Anonymous]

@Greg_Deckler Again, thanks for the response ! I think I see the whole idea, but i don't think it really works in my case, since I have two sellers. I feel like your solution fits the case where I just have a "sellers" table and a "sales" tables like the majority of people using PBI. I don't really see how I can make my problem work, maybe by creating a conditionnal table using DAX measure ? I really struggle with this issue haha

Thank you

Max Dedieu

[Greg_Deckler]

@Anonymous I still am not sure what you are trying to accomplish. I feel like I have half of the information needed.