Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 0
I 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!!
Solved! Go to Solution.
@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]);
)
@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]);
)
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!
Hi @Anonymous
The two formula do the same thing.
Best Regards
Maggie
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |