Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
charleshale
Responsive Resident
Responsive Resident

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

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

 

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

charleshale
Responsive Resident
Responsive Resident

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

View solution in original post

5 REPLIES 5
charleshale
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

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

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

Prateek97
Resolver III
Resolver III

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.