Relationship on a column containing a match

[billybazinga]

Hi everyone,

I was hoping you could share some guidance: I have a dataset with a large number of tables and am having issues with two specifically:

Table 1

Date	Amt	Receipt	Manufacturer
1-Jan	15	ABC	MFN 1: Part wert, MFN 3: Part ert
2-Jan	10	DBC	MFN 1: Part xxx, MFN 4: Part dey
3-Jan	25	BHY
4-Jan	20	YUH	MFN 5, MFN 1
5-Jan	35	EDG	MFN 4
6-Jan	30	UIO	Example 3

Table 2

Manufacturer Name

MFN 1

MFN 2

MFN 3

MFN 4

MFN 5

I would like to create a relationship between Table 2 and Table 1, with a view to use the Manufacturer field from Table 2 in a slicer to identify, as an example, the total amount per potential manufacturer (understanding that the amount 15 would forever be listed against both "MFN 1" and "MFN 3". I would have other slicers from other tables and don't really have an ability to change the structure of Table 1, other than adding columns.

Any clever ideas?

[v-easonf-msft · Accepted solution]

Hi, @billybazinga 

If you need to filter table data, you can consider applying a measure to visual filter pane.

Measure = 
var a=IF(CONTAINSSTRING(MAX(Table1[Manufacturer]),MAX(Table2[Manufacturer Name])),1,0)
return IF(ISFILTERED(Table2[Manufacturer Name]),a,1)

Best Regards,
Community Support Team _ Eason

[tamerj1]

Hi @billybazinga 

I don't think creating a relationship is feasible. However you can create measures like

Amount =
SUMX (
    Table1,
    SUMX (
        Table2,
        IF (
            CONTAINSSTRING ( Table1[Manufacturer], Table2[Manufacturer] ),
            Table1[Amt]
        )
    )
)