Why arent these 2 addcolumns equivalent? The treatas one fails to recognize row context

[charleshale]

Hi.  I have 2 (very large) simple tables of email addresses.   Let's call these Table1 and Table2.  They each have a column called [Email] that is an email address (ie a string).   The 2nd table also has a column of a value of 1 or 0 for if the email address was migrated.   I am trying to add to table 1 a calc of how many times a matching email address in table 2 was migrated.

I am using a simple sum with treatas since the tables are too big for a physical relationship and the email addresses are not unique in each table.

Sum Fails= 
CALCULATE(
    Sum(Table2[ColumnToCount]), 
    TREATAS(values(Table1[Email]),Table2[Email]),
    Allexcept (Table2, Table2[Email]) )

Why is the above returning the sum of all rows in Table2[ColumnToCount] instead of the treatas working to say "create relationship between the 2 tables' email addresses" and then the allexcept saying "and make sure to not skip the row context in table2"?    

This must be awfully basic and I should know the answer but maybe I'm getting tired from looking at it so long.

PS I know I can use the following to make it work but why wouldnt the above work as well?

Count_Works= CALCULATE(sum(Table2[ColumnToCount]), FILTER(Table2, Table2[Email]=Table1[Email]))

[Prateek97 · Accepted solution]

Hi @charleshale ,

I think I created a Measure, while you created a calculated column. It is not working as a Column but working in the measure.

So I think what is happening is that the VALUES is passing the complete Email Column from the first table when used as a column while when used as a measure it passes only the specific email by the row context. So this gives us the point of failure of the code.

I tried to modify it as below. It seems to be working. Please check now.

Sum Fails =

CALCULATE(

 SUM(Email2[Migrated]), 

 TREATAS(
     FILTER(VALUES(Email1[Email])
                ,Email1[Email]=EARLIER(Email1[Email]))
,Email2[Email]))

[charleshale · Accepted solution]

Note @Prateek97 The following code works a lot faster given the table set --- based on your code -- just substituting VAR for earlier.  Thank you for the help.

SUM_Fastest =
VAR _1 = Table1[Email]  //Table1 is Table on which column is being added
RETURN
    CALCULATE (
        SUM ( Table2[Migrated] ),    //Table2 is the lookup Table being aggregated into Table1
        TREATAS (
            FILTER ( VALUES ( Table1[Email] ), Table1[Email] = _1 ),
            Table2[Email]
        )
    )

[charleshale]

I thought it might help force a row context because I'm not getting one the way you did above ---- which is strange.  I must be missing something basic or it could a bug.   I really cant imagine why the filter version performs and TREATAS doesnt

[Prateek97 · Accepted solution]

Hi @charleshale ,

I think I created a Measure, while you created a calculated column. It is not working as a Column but working in the measure.

So I think what is happening is that the VALUES is passing the complete Email Column from the first table when used as a column while when used as a measure it passes only the specific email by the row context. So this gives us the point of failure of the code.

I tried to modify it as below. It seems to be working. Please check now.

Sum Fails =

CALCULATE(

 SUM(Email2[Migrated]), 

 TREATAS(
     FILTER(VALUES(Email1[Email])
                ,Email1[Email]=EARLIER(Email1[Email]))
,Email2[Email]))

[charleshale]

Brilliant.   Yes! This is exactly what happened.   Good thinking.  Thank you. 

[Prateek97]

Hi @charleshale ,

What was the reason to add ALLEXCEPT in the first place? Because TREATAS will act as a relationship between the two columns passed and the context will also be setup by default. The following works for me.

Sum Fails =

CALCULATE(

    Sum(Email2[Migrated]), 

    TREATAS(values(Email1[Email]),Email2[Email]))