Finding Intersection between two columns

[Griffin_BI]

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

ID

1

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!

[Griffin_BI]

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.

[OwenAuger]

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

[Griffin_BI]

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

[OwenAuger]

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

[Jihwan_Kim]

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

[OwenAuger]

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