topic Finding Intersection between two columns in DAX Commands and Tips

Finding Intersection between two columns

Griffin_BI — Sun, 06 Feb 2022 20:39:31 GMT

I have two tables:

Table1:

ID	Letter
1	a
2	b
3	c
4	d
5	e
6	f
7	g
8	h
9	i

And a virtual table, Table 2

How do I find the intersection (i.e. all rows in Table1 that has an ID in Table2). There are a large number of rows, so the DAX function must be efficient.

Thanks!

Re: Finding Intersection between two columns

OwenAuger — Mon, 07 Feb 2022 00:04:42 GMT

Hi there,

Based on what you've posted, I would suggest using TREATAS to apply Table2 as a filter (I'm assuming Table2 has a single column).

For example, if you need an expression that returns the rows of Table1

CALCULATETABLE ( Table1, TREATAS ( Table2, Table1[ID] ) )

What is the ultimate goal, i.e. where would the intersecting rows of Table1 be used?

Regards,

Owen

Re: Finding Intersection between two columns

Jihwan_Kim — Mon, 07 Feb 2022 04:20:09 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

Intersect table =
CALCULATETABLE ( Table1, INTERSECT ( VALUES ( Table1[ID] ), Table2 ) )

Re: Finding Intersection between two columns

Griffin_BI — Mon, 07 Feb 2022 14:58:17 GMT

Thanks Owen, but I couldn't use this as a solution.

TREATAS() requires the name number of columns in the table I believe.

Can I not use CONTAINS?

The ultimate goal is to filter a table based on a random sample (Table2). Two parallel calculations have to be done from the same random sample so I'm getting the sample as a concatenated list and then building a virtual table using that list inorder to filter another virtual table.

Re: Finding Intersection between two columns

OwenAuger — Mon, 07 Feb 2022 20:06:05 GMT

That's right, TREATAS requires the same number of columns as column references provided.

If Table2 contains more than just the ID column, then just use SELECTCOLUMNS to select the ID column:

CALCULATETABLE ( Table1, TREATAS ( SELECTCOLUMNS ( Table2, "ID", Table2[ID] ), Table1[ID] ) )

It is possible to use CONTAINS with FILTER, but performance is likely to be worse.

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Regards,

Owen

Re: Finding Intersection between two columns

Griffin_BI — Tue, 08 Feb 2022 16:25:55 GMT

Not sure what I'm doing wrong here. I get an error saying that a multiple values were supplied when a single value was expected?

VAR _indextbl = GENERATESERIES(1, PATHLENGTH([Sample List]))

VAR _Table2 =

GENERATE(_indextbl, ROW("@ID", PATHITEM(_indextbl, [Value], 0)))

VAR _SampleTbl =

CALCULATETABLE(

ALLSELECTED('Table1'),

TREATAS(

SELECTCOLUMNS(Table2, "ID", [@ID]),

'Table1'[ID]

)

VAR _Xbar =

AVERAGEX(

SUMMARIZE(

_SampleTbl,

'Calendar'[Month],

"@X", AVERAGE(LengthOfTime)

[@X])

RETURN

_XBar

Re: Finding Intersection between two columns

OwenAuger — Tue, 08 Feb 2022 20:25:21 GMT

One problem I can see is that the first argument of PATHITEM is currently _indextbl, but it should be a vertical bar delimited string, presumably [Sample List].