topic Re: Relationship on a column containing a match in DAX Commands and Tips

Relationship on a column containing a match

Anonymous — Thu, 14 Jul 2022 13:24:37 GMT

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?

Re: Relationship on a column containing a match

tamerj1 — Thu, 14 Jul 2022 14:02:53 GMT

Hi Anonymous

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] ) ) )

Re: Relationship on a column containing a match

v-easonf-msft — Thu, 28 Jul 2022 02:42:51 GMT

Hi, Anonymous

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