DAX simple 2 table Intersect .... but via an inactive relationship!

[charleshale]

Let's say I have 2 tables both of email address: Table1: Email Subs ("Subs") and Table2: Registered Users ("Registereds").   The intersection between these is straight forward, and I do the following -- which is 2 VARs of distinct columns and the intersection, as below:

What if, however, the second table is instead a user number and I dont want to lard up the 2nd table with a lookupvalue (that would add a column with the usernumber's email....) and instead want to do that in memory, and the lookupdate of user # and user email is an inactive relationship.   What's the best path?    Var with lookupvalue fails on the indirect.   I suppose I could do a calculatetable and then select columns?     

Any advice for what's the most efficient?   Some kind of related function?

Thank you

Charlie

[amitchandak · Accepted solution]

@charleshale , if both tables have email ids you can use  intersect

Var _tab = intersect(all(Table1[email]), All(Table2[email]))

return

calculate(count(Table1[email]), filter(Table1,Table1[email] in _tab))

But if the tables are joined. then the inner join will give common values

in a visual table2[email] and count(Table1[email]) should do

[charleshale]

Hi, @amitchandak, ever present super-user extraordinaire.

Here's a sample file

The intersect would be only 1 row  between tables 1 and 2 (Jim@yahoo.com)

The question is what's the best way to code an intersect function if I want to use a related table #3 as the lookup instead of the obvious way of simply adding an email address column to table2 via lookupvalue()?   Sorry - important note: assume Table3 is only an inactive relationship so  I might need to use TreatAs or UserRelationship

Why I persist in trying to find harder ways to do things is a separate question altogether!

[amitchandak]

@charleshale , I am not 100% sure, I got what output you want. Please find the attached file after the signature, if that can help

Please note that the link you have shared is having other content too. Please hide that if everything is not a public file.

[charleshale]

That would work -- nice job -- but how would you do it user intersect ()?  The question I am fundamentally interested in is the intersect between a column in 2 tables when the relationship is indirect.   For example, in the real world, counting the overlap between registered users and email subs when one is an email address and the other expresses itself as a hash that I have to look up through an intermediate table.

[charleshale · Accepted solution]

So, here's the best I have been able to get: merge 2 tables thru a CROSSJOIN and then select the column.   That works.........Not so elegant AND PAINFULLY SLOW but does the job

~overlap_
VAR _table1 =
    DISTINCT ( Table1[Email] )
VAR _table2 =
   SELECTCOLUMNS(
       CROSSJOIN(Table2,  LookupTable),   //table2 has registered users by # 
       "RegUsersEmail", LookupTable[Email])
        
        
VAR _Intersect =
    INTERSECT ( _table1, _table2 )
RETURN
    COUNTROWS ( DISTINCT ( _Intersect ) )

[charleshale]

PS a crossfilter would work but that requires a calculate function.     

[amitchandak]

@charleshale , Yes crossjoin is another good option. Just be careful, it might be costly on bigger data.

[amitchandak · Accepted solution]

@charleshale , if both tables have email ids you can use  intersect

Var _tab = intersect(all(Table1[email]), All(Table2[email]))

return

calculate(count(Table1[email]), filter(Table1,Table1[email] in _tab))

But if the tables are joined. then the inner join will give common values

in a visual table2[email] and count(Table1[email]) should do

[amitchandak]

@charleshale , Not very clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can create a common email Table 

Measure =

var _T1 = distinctcount(Table1[email])

var _T2 = distinctcount(Table2[email])

return

countx(filter(summarize(Table3, Tabl3[email],"_1",_T1, _2,_T2), [T1] >0 && [T2]>0),[email])

should give you common between Table1, and Table 2