Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

Hi @Anonymous 

The two formula do the same thing.

 

Best Regards

Maggie

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.