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
Super 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
      Community Support
      6 years ago

      Hi Anonymous 

      The two formula do the same thing.

       

      Best Regards

      Maggie