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

  • 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

  • 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

      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

        Hi Anonymous 

        The two formula do the same thing.

         

        Best Regards

        Maggie