Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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]);  
)

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    The two formula do the same thing.

     

    Best Regards

    Maggie