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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ePros
Frequent Visitor

Create Expression counting a single column but using various qualifying criteria

Is there a way to write an Expression to calculate the total from "report (1) column (3) with criteria (a) AND report (1) column (3) with criteria (b) "What I am trying to do is count the number of calls where lead source is "A" and call duration > 30 seconds AND where lead source is "B" and call duration > 15 Right now I can do the first part in one measure and the 2nd part in another, is there a way to count them in a single measure? Technically I want to count 3 in one so actually include lead source "C" and call duration >5

1 ACCEPTED SOLUTION

@ePros 

pls try this

Measure = countx(FILTER('Table','Table'[Source]="Referral"&&'Table'[Call Duration]>30 ||'Table'[Source]="Commercial"&& 'Table'[Call Duration]>15||'Table'[Source]="Advertisement"&&'Table'[Call Duration]>5),'Table'[Calls])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

could you pls proivde some sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




CallsSourceCall Duration      
555-555-5555Referral65      
555-555-5555Referral20  Count Where Source IN { "Referral'},  
555-555-5555Referral65  Call Duration >30  
555-555-5555Referral13      
555-555-5555Referral85  3   
111-111-1111Commercial82      
111-111-1111Commercial61      
111-111-1111Commercial28  Count Where Source IN { "Commercial'}, 
111-111-1111Commercial35  Call Duration >15  
111-111-1111Commercial12      
111-111-1111Commercial3      
111-111-1111Commercial11  5   
111-111-1111Commercial82      
999-999-9999Advertisement73      
999-999-9999Advertisement2      
999-999-9999Advertisement35  Count Where Source IN { "Advertisement'}, 
999-999-9999Advertisement1  Call Duration >5   
999-999-9999Advertisement13      
999-999-9999Advertisement4  3   
         
In This example I want a single returned value of 11      

@ePros 

pls try this

Measure = countx(FILTER('Table','Table'[Source]="Referral"&&'Table'[Call Duration]>30 ||'Table'[Source]="Commercial"&& 'Table'[Call Duration]>15||'Table'[Source]="Advertisement"&&'Table'[Call Duration]>5),'Table'[Calls])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you that worked well. Obviously that was not the data I was working with but I was to get a single total of the data where each line was tested for 3 conditions to determine if counted and each condition verified data from 3 separate columns. I was considering returning to the table and adding a column and using IF statements then just adding from there., I appreciate the help.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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