Forum Discussion

Griffin_BI's avatar
Griffin_BI
Frequent Visitor
4 years ago

Finding Intersection between two columns

I have two tables:

 

Table1:

 

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

 

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!

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 years ago

    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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

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

     

  • Griffin_BI's avatar
    Griffin_BI
    Frequent Visitor
    4 years ago

    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's avatar
      OwenAuger
      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.

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

       

      Regards,

      Owen

      • Griffin_BI's avatar
        Griffin_BI
        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