Forum Discussion

Frequent Visitor

4 years ago

Finding Intersection between two columns

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

Super User

4 years ago

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.

Regards,

Owen

Frequent Visitor

4 years ago

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
Super User
4 years ago
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].