Forum Discussion
Filter on a Summarized table
Hi
I have a table with customer ID and a 1 or 0 depeding if the email or phone is valid or not, like this:
CustomerID ValidEmail Valid Phone
1234 1 1
1234 1 0
1234 0 1
1235 1 1
1235 0 0
1236 1 1
1237 1 1
1238 1 1
1239 1 0I need to count the DISTINCT CutomerId that has a valid email,phone and Email and Phone.
For this, I've created a summarized table as a measure:
TotalValidEmails=
Var GroupedTable = SUMMARIZE (
'Table1';
'Table1'[CustomerID];
"Grouped_Valid_Email";MAX('Table1'[ValidEmail]);
"Grouped_Valid_Phone";MAX('Table1'[ValidPhone])
)
Return
CALCULATE(
COUNTX(GroupedTable;'Table1'[CustomerID]); --Am I counting the right field?
[Grouped_Valid_Email]=1 --How do I filter only the values that have Grouped_Valid_Email=1? This syntax is incorrect according to PowerBi
)The Summarized table seems to be correct, but I need to count the CustomerID field, filtering when Grouped_Valid_Email=1, Grouped_Valid_Phone=1 and when (Grouped_Valid_Phone=1 AND Grouped_Valid_Email=1)
How do I filter this? (BTW I need to do this in a measure)
Thanks!!
Anonymous , Try one of the two
TotalValidEmails= Var GroupedTable = SUMMARIZE ( 'Table1'; 'Table1'[CustomerID]; "Grouped_Valid_Email";MAX('Table1'[ValidEmail]); "Grouped_Valid_Phone";MAX('Table1'[ValidPhone]) ) Return CALCULATE( COUNTX(filter(GroupedTable;[Grouped_Valid_Email]=1 );[CustomerID]); ) TotalValidEmails= CALCULATE( COUNTX(filter( SUMMARIZE ( 'Table1'; 'Table1'[CustomerID]; "Grouped_Valid_Email";MAX('Table1'[ValidEmail]); "Grouped_Valid_Phone";MAX('Table1'[ValidPhone]) );[Grouped_Valid_Email]=1 );[CustomerID]); )
3 Replies
- amitchandakSuper User
Anonymous , Try one of the two
TotalValidEmails= Var GroupedTable = SUMMARIZE ( 'Table1'; 'Table1'[CustomerID]; "Grouped_Valid_Email";MAX('Table1'[ValidEmail]); "Grouped_Valid_Phone";MAX('Table1'[ValidPhone]) ) Return CALCULATE( COUNTX(filter(GroupedTable;[Grouped_Valid_Email]=1 );[CustomerID]); ) TotalValidEmails= CALCULATE( COUNTX(filter( SUMMARIZE ( 'Table1'; 'Table1'[CustomerID]; "Grouped_Valid_Email";MAX('Table1'[ValidEmail]); "Grouped_Valid_Phone";MAX('Table1'[ValidPhone]) );[Grouped_Valid_Email]=1 );[CustomerID]); )- AnonymousNot applicable
Thanks. The first one worked!
Do you know what is the differencence between these two:
CALCULATE( COUNTX(filter(GroupedTable;[Grouped_Valid_Email]=1 );[CustomerID]); )CALCULATE( COUNTX(filter(GroupedTable;[Grouped_Valid_Email]=1 );'Table1'[CustomerID]); )Both gives me the same result.. but I'm unsure if I am counting different things
Thanks!
- v-juanli-msftCommunity Support
Hi Anonymous
The two formula do the same thing.
Best Regards
Maggie