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
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]))
Solved! Go to 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]))
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]
)
)
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]
)
)
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.
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]))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |