Forum Discussion

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