Power bi measure count with filter

In this Power bi tutorial, we will learn Power Bi measure count with filter. And also we will discuss the below points:

• Power bi measure count with filter
• Power bi measure count rows with a filter
• Power bi measure distinct count with filter
• Power bi measure count with multiple filters
• Power bi measure count without filter
• Power bi measure calculate count filter

Power bi measure count with filter

In Power bi, we can choose a column as per our requirement, then we will use the COUNT() to count the number of cells, based on some conditions we will filter the column or table by using the filter() function.

Similarly, we will use the Countrows() and distinct count () with filter.

Here we will see how to count the names, then we will filter based on the occurrence.

We will use the below sample table to count the names and then filter the names based on a single occurrence and multiple occurrences.

For example, here you can see AAA, BBB, CCC, and GGG are occurring multiple times, except these four names, all other names are single occurrence.

So here we will create two measures, in both the measures it will count the names, count=1, then it is a single occurrence, and if count> 1 then it is a multiple occurrence measure.

• Load the data using get data.
• Click on the New measure from the ribbon, to calculate the single occurrence name.
• Then write the below measure:

Single_Occurrence = 
VAR A = SUMMARIZE('Data','Data'[Name],"Count1",COUNT('Data'[Name]))
RETURN CALCULATE(COUNT('Data'[Name]),FILTER(a,[Count1]=1))

• To create another measure for the multiple occurrences, click on the new measure.
• Then write the below measure:

Multiple_Occurrence = 
VAR A = SUMMARIZE('Data','Data'[Name],"Count1",COUNT('Data'[Name]))
RETURN CALCULATE(COUNT('Data'[Name]),FILTER(a,[Count1]>1))

• Now to check the measures, create two table visual, then in one table visual add name column and single occurrence measure.
• And in another table visual add the name column and multiple occurrence measure.

Read How to create a Measure based on Slicer in Power BI

Power bi measure count rows with filter

Here we will see how we will filter the column based on condition by using the Filter() function and then count the rows using COUNTROWS() DAX function.

We will use the below sample table to count the rows by comparing and filtering the two columns.

Now we will create a measure that will count the rows based on column3 = no match and column 2 values are greater than column 1 values.

• Load the data using get data in power bi desktop.
• Click on the new measure from the ribbon.
• Then write the below measure:

Count = COUNTROWS(FILTER(Table2,Table2[Column3]="No match" && Table2[Column2]>Table2[Column1]))

• Now to check the measure, select the table visual from the visualization pane.
• In the value field, drag and drop the column1, column2 column3 and count measure

Read Power bi measure switch statement with examples

Power bi measure distinct count with filter

Here we will see how to use Distinctcount() DAX function after filter the table using the filter() function in power bi.

We will use the below sample table to count the distinct clients and filter based on the condition whose answer = Yes.

From the above table, the expected output is A = yes and C = Yes.

• Load the data using get data.
• Then click on the new measure from the ribbon to get the expected output.
• The write the below measure:

Clients who answered Yes = 
CALCULATE (
DISTINCTCOUNT ( 'Data 1'[Client] ),
FILTER('Data 1','Data 1'[Answer] = "Yes"
))

• Now to check the measure, select the table visual from the visualization pane.
• In the value field, drag and drop the Client column, answer column, and client who answered Yes measure from the field pane.

Read Power BI Measure Filter

Power bi measure count with multiple filter

Here we will see how to use COUNTROWS() with multiple filters() in the Power Bi.

We will use the below sample data to count rows based on multiple criteria, and filter all the males with grades c, d, and e.

The expected output from the above table is AAA, M, C; LLL, M, C; RRR, M, E.

• Load the data using get data.
• Click on the new measure from the ribbon.
• Then write the below measure:

MaleCDEcount = CALCULATE(COUNTROWS('Table'), FILTER('Table','Table'[Gender]="M"), FILTER('Table',
 'Table'[Grade]="C" || 'Table'[Grade]="D" || 'Table'[Grade]="E"))

• Now to check the meaure, Click on the table visual from the visualization pane.
• In the value field, drag and drop the name, gender, grade columns and MaleCDEcount measure from the field pane.

Read Power BI Measure multiply with examples

Power bi measure count without filter

Here we will see how to count without filter by using the DIstinctCount() in the Power bi.

We will use the below sample table to filter the company forest and service to get the number of servers in that company and forest without that service.

Here we will create three slicers for company, forest, and service name, when we select a company, forest, and service name, it will show the count of company, system, and forest without that service.

For example, if I select the company Fabrikam, the forest is fabrikam.org and we select a service svc1 because this company has taken two services.

So it will show company with service =1 and company without service =1, for this, we will create two measures to count with service and count without service.

• Load the data using get data
• Click on the new measure to calculate system with service.
• Then write the below measure.

Count with service = DISTINCTCOUNT ( 'Table 1'[SystemName] )

• Click on the new measure to calculate system without service.
• then write the below measure:

Count without service = 
CALCULATE(
    DISTINCTCOUNT('Table 1'[SystemName]),ALLEXCEPT('Table 1','Table 1'[Company],'Table 1'[forest])) - [Count with service]

• Now create a three slicer for company, forest and servicename column.
• Then create two card visual for count with service and count without service.
• Now select the Fabrikam company, select the forest, and then select service name svc1.
• Now we can see the result in the card visual, with service =1 and without service =1.

Read Power bi sort by measure

Power bi measure count based on filter

Here we will see how to count based on filter using Power bi measure.

We will use the below sample to calculate the percentage of returned sales and then distinct count the name based on the percentage of returned sales.

• Load the data using get data from the ribbon in power bi desktop.
• We will calculate the percentage of returned sales.
• Click on the new measure from the ribbon.
• Then write the below measure:

Percentage of returned sales = DIVIDE(SUM(Table1[Return]), SUM(Table1[Sales]),0)

• Now we will count the the person whose percentage of sales is over than 15% or 0.15.
• Click on the new measure from the ribbon.
• Then write the below measure.

Count of returned sales = CALCULATE(DISTINCTCOUNT('Table1'[Name]),FILTER('Table1','Table1'[Percentage of returned sales]>0.15))

• Now to check the measure, click on the table visual from the visualization pane.
• Then in the value field, drag and drop the name, sales, return columns, percentage of returned sales, and count of returned sales.

You may like the following Power BI tutorials:

• Power bi measure divide
• Power BI Measure Date
• Countif function in Power BI Measure
• Power BI Measure SUM
• Power bi measure by category
• Power BI DAX SUM and SUMX function

In this Power bi tutorial, we learned power bi measure count with filter. And also we discuss the below points:

• Power bi measure count rows with filter
• Power bi measure distinct count with filter
• Power bi measure count with multiple filter
• Power bi measure count without filter
• Power bi measure count based on filter