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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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