More than one ID per cell

Question

Hi there,I`m quite new to power BI. The internet doesn`t help, so I ended here 😉I have a file with diffrent releations. Lets call it customers and Products.We use power BI as some kind of sorting tool. thare are not much changes but it gives us a nice overview.&nbsp;We want to see which customer gets which product.I have a table with the products, a table with customers and a table for the releation between the two.&nbsp;Product looks like:&nbsp;Product IDProduct_NameProdct_SubnameData1Data2BiBillirubinControl 1set1set2TNTroponinControl1set1set2&nbsp;Customer looks likeCustmerIDNameLastnamestreetCityState1SimonMulerStreet 1LondonUK2SusanneSaltStreet 2HamburgDEThe relation looks like:Product_NameCustomer_IDBi1; 3; 10TN2&nbsp;This is an example.The Product ID isnot be uniq! I have an:m relation here wich qorks fine!My problem is the relation table with more than one data set in one cell.I can`t split the coloum in power BI, if I do, I can make only one relation to the customer table.On the other hand power BI can`t sort the data correctly if more than one set is in the cell.&nbsp;We have just startet our little project and jet we don`t have too many items listed, so we can still change some things.&nbsp;Any help / advise?&nbsp;Many thanks in advanced.&nbsp;BRKamai&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Accepted Answer

Hi Kamai&nbsp;,
Based on the testing, transform the relation table data. Select split column by delimiter.

Unpivot only selected columns.

Rename the column name.

Remove Attribute column.

Select close and apply.

Then, creating the new measure to filter the product based on customer.
Measure = 
var _customer = SELECTEDVALUE('Relation Table'[Customer_ID])
var _product = SELECTEDVALUE('Product'[Product ID])
var _result = CALCULATE(MAX('Relation Table'[Product_Name]), FILTER('Relation Table', 'Relation Table'[Customer_ID] = _customer))
RETURN
IF(_product = _result, 1, 0)
Drag the measure to the table visual Filters pane and set show item is 1.

Select the customer ID. The result is shown below.

Best Regards,
Wisdom Wu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

More than one ID per cell

2 Replies

Related content

Cells Offset

Merge Cells in Matrix

Previous Row Cell multiplied with This Row Cell

Merge row cells

Notebooks not displaying markdown cells

Product ID	Product_Name	Prodct_Subname	Data1	Data2
Bi	Billirubin	Control 1	set1	set2
TN	Troponin	Control1	set1	set2

CustmerID	Name	Lastname	street	City	State
1	Simon	Muler	Street 1	London	UK
2	Susanne	Salt	Street 2	Hamburg	DE

Product_Name	Customer_ID
Bi	1; 3; 10
TN	2