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

CountIF Measure with Multiple Filter Critera

I have a table where Store Numbers are listed on the left, and then a calculated column determines whether a store is IP, IP & Analog, or Analog

 

IP & Analog would count both for IP and for Analog

 

I want to have a column that just shows IP when it is IP or IP & Analog, and Analog if it's Analog or IP & Analog

 

I then want to get a % IP by the entire count by using these 2 measures.

 

Here is what I Tried but I know I"m doing something wrong

IP Count = CALCULATE(COUNT('IP and Analog'[IP or Analog]),'IP and Analog'[IP or Analog]="IP", 'IP and Analog'[IP or Analog]= "IP & Analog")
 
Sample Table:

Store   IP Analog

1234     IP
1235     Analog
1236     IP & Analog
1237     IP
 
The count would look something like this
IP Count = 3 (IP, IP & Analog, IP)
Analog Count = 2 (Analog, IP & Analog)
1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

Based on my test, you could refer to below measures:

IP count = CALCULATE(COUNT(Table1[IP Analog]),FILTER('Table1','Table1'[IP Analog]="IP"||'Table1'[IP Analog]="IP & Analog"))
Analog count = CALCULATE(COUNT(Table1[IP Analog]),FILTER('Table1','Table1'[IP Analog]="Analog"||'Table1'[IP Analog]="IP & Analog"))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

Based on my test, you could refer to below measures:

IP count = CALCULATE(COUNT(Table1[IP Analog]),FILTER('Table1','Table1'[IP Analog]="IP"||'Table1'[IP Analog]="IP & Analog"))
Analog count = CALCULATE(COUNT(Table1[IP Analog]),FILTER('Table1','Table1'[IP Analog]="Analog"||'Table1'[IP Analog]="IP & Analog"))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jsh121988
Employee
Employee

 

I would suggest creating a calculated column for IP and Analog, then SUM each individually. The problem is IP & Analog overlaps into both catagories.

IP_Bool = IF('IP and Analog'[IP or Analog] IN {"IP", "IP & Analog"}, 1, BLANK())
Analog_Bool = IF('IP and Analog'[IP or Analog] IN {"Analog", "IP & Analog"}, 1, BLANK())

If you want to do 2 measures instead, then just use this filter logic:

 

 

IP_Count = 
CALCULATE(
COUNTROWS('IP and Analog'), FILTER('IP and Analog', 'IP and Analog'[IP or Analog] IN {"IP", "IP & Analog"}) )
Analog_Count = 
CALCULATE(
    COUNTROWS('IP and Analog'),
    FILTER('IP and Analog', 'IP and Analog'[IP or Analog] IN {"Analog", "IP & Analog"})
)

 

 

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.