Filter on a Summarized table

[Anonymous]

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!!

[amitchandak · Accepted 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]

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

Hi @Anonymous 

The two formula do the same thing.

Best Regards

Maggie