Power BI Distinct Count with filter [With 5 Examples]

In this Power Bi tutorial, we will see how to work with Power BI Distinct Count with a filter in Power Bi. And we will also find the distinct count value with multiple filters in Power Bi. Also covered below topics:

• Power BI distinct count with filter nonblank
• Power BI distinct count with the filter all selected
• Power BI distinct count with the filter all
• Power BI distinct count without filter
• Power BI distinct count with date filter

Power BI distinct count with filter

Let us see how we will use the Power Bi distinct count with a filter to count the distinct values in Power Bi.

In this example, we use the financials table to filter the distinct values using the Power Bi distinct count function in Power Bi.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Distinct Values = CALCULATE (
DISTINCTCOUNT ( financials[Product] ),
FILTER(financials,financials[Country] = "Canada"
))

Where,

1. Distinct Values  = Measure name
2. financials = Table name
3. Country, Product  = Column names

• Now in the report view, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product and sales value fields from the field pane, and in the card visual drag and drop the created Measure value like below.

• In the below screenshot, you can see that the card visually displays the filtered distinct value count based on the condition applied.

This is an example to work with the Power Bi distinct count with a filter in Power Bi.

Power bi distinct count with filter multiple filters

Here we will see how to display the distinct count values with multiple filters using the Power Bi Filter function in Power BI,

In this example, we will find the distinct values of the sales field based on the year, it will display a distinct count for the sales value greater than or equal to 1000000 for the Year 2021.

• Load the table data into the Power Bi desktop using the get data option. From the ribbon click on the new measure option and use the below formula:

Multiple Filters = 
     COUNTROWS(
    DISTINCT(
        FILTER( 'financials', YEAR( financials[Date]) = 2021 && [ Sales] >= 1000000)
      )
    )

Where,

1. Multiple Filters  = Measure name
2. financials = Table name
3. Date, Sales= Column names

• In the Power Bi report view, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product, sales value, and Year fields from the field pane, and in the card visual drag and drop the created Measure value as shown below:

The screenshot below displays the filtered distinct value based on the condition applied in the card visual.

This is how to display the distinct count values with multiple filters in Power BI.

Read Power BI Conditional Formatting Based on Text

Power BI distinct count with filter nonblank

Here we will see how to count nonblank values using the Power Bi distinct count function in Power Bi,

In this example, we will count the non-blank values for the discounts column presented in the financials table data.

Log in to the Power Bi desktop and use the get data option to load the data. Click on the new measure option from the ribbon and apply the below formula:

Non Blank Values = CALCULATE (
    DISTINCTCOUNT ( financials[Discounts] ),
    KEEPFILTERS ( NOT ISBLANK ( financials[Discounts] ) )
)

Where,

1. Non-Blank Values = Measure name
2. financials = Table name
3. Discounts = Column names

• Select the table visual and the card visual from the visualization in a report view.
• In the table visual, drag and drop the Product, discounts, and country fields from the field pane, and in the card visual drag and drop the created Measure value.

The screenshot below displays the non-blank values count for the discounts column presented in the financial table data.

This is how to count the nonblank values using the Power Bi distinct count function.

Power BI distinct count with the filter all selected

Here we will learn how to count the distinct values using the Power BI distinct count with the filter all selected in Power Bi.

In this example, we will count the distinct values for the country column presented in the table by using the Filter function in Power Bi.

Load the data to the Power Bi desktop using the get data option, and then select the New measure option and use the below formula:

filter all selected = CALCULATE(DISTINCTCOUNT(financials[Country]),FILTER(ALLSELECTED('financials'),[Year]=MAX(financials[Year])))filter all selected = CALCULATE(DISTINCTCOUNT(financials[Country]),FILTER(ALLSELECTED('financials'),[Year]=MAX(financials[Year])))

Where,

1. filter all selected = Measure name
2. financials = Table name
3. Country = Column names

• Select the table visual and the card visual from the visualization in the Power Bi report view.
• In the table visual, drag and drop the Product, sales, and country fields from the field pane, and in the card visual drag and drop the created Measure value.

In the below screenshot, you can see that the card visually displays the distinct count value for the filtered country column.

This is how to count the distinct values using the Power BI distinct count with the filter all selected.

Power BI distinct count with the filter all

Here we will how to work with Power BI distinct count with the filter all in Power Bi.

This example will help you to count the distinct values for the sales column using the Power Bi filter function in Power BI.

• Log in to the Power Bi desktop and use the get data option to import the data table. Click on the New measure option from the ribbon and use the below-mentioned formula in the formula bar.

filter all = CALCULATE(DISTINCTCOUNT(financials[ Sales]),FILTER(ALL('financials'),[Year]=MAX(financials[Year])))

Where,

1. filter all = Measure name
2. financials = Table name
3. Sales, Year = Column names

• Select the table visual and the card visual from the visualization in the Power Bi report view.
• In the table visual, drag and drop the Product and sales fields from the field pane, and drag and drop the created Measure value in the card visual.

The screenshot below displays the distinct count value in the card visual based on the condition applied for the sales column.

This is an example to find Power BI’s distinct count with the filter all.

Power BI distinct count without filter

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

In this example, we will use two slicers for the country and year, when we select country and year, in the card visual it displays the sales count and sales without the selected value.

• Load the data using get data and Click on the new measure to calculate the sales value with the filter. Then use the below-mentioned measure.

Sales count = DISTINCTCOUNT ( financials[ Sales] )

Where,

1. Sales count = Measure name
2. financials = Table name
3. Sales = Column names

Create another new measure to calculate the sales value without the selected value count. Then apply the below-mentioned measure.

Sales without Selected Value = 
CALCULATE(
    DISTINCTCOUNT(financials[Product]),ALLEXCEPT('financials',financials[Country],financials[Year])) - [Sales count]

Where,

1. Sales without Selected Value = Measure name
2. financials = Table name
3. Product, Country, and Year = Column names
4. Sales count = Existing measure value

• Now in the Power Bi report view select two slicer visuals from visualization and drag and drop the country field and year field.
• select two card visuals and drag and drop the created measured values in it as below:

• Now select any country value from the country visual and year value from the year visual, so it filters the sales value based on the condition applied.
• In the below screenshot, you can see that the card visually displays the sales count value, and also the sales value without the selected value.

This is how to count without filter by using the Power Bi DIstinctCount() in the Power bi.

Power BI distinct count with date filter

Here we will see how to work with Power BI distinct count with date filter using the Power Bi DistinctCount() in the Power bi.

In this example, we will use a date slicer to display the distinct count value of the product based on the date selection.

Load the data using get data and Click on the new measure to calculate the distinct count value by date. Then use the below-mentioned measure.

Where,

By date = 
CALCULATE (
    DISTINCTCOUNT (financials[ Sales] ),
    ALLSELECTED ( financials[Date].[Date] ),
    FILTER (
        financials,
        financials[Date]
    )
)

1. By date = Measure name
2. financials = Table name
3. Sales,Date = Column name
4. DISTINCT COUNT = Function name

• Now in the Power Bi report view select slicer visual from visualization and drag and drop the date field.
• Select Table visual and drag-drop the product name, sales field, and the created measure value
• Select card visuals and drag and drop the created measured values in it as below:

In the below screenshot, you can see that the card visual displays the sales count based on the dates selected from the date slicer visual in Power Bi.

This is an example to work with Power BI distinct count with a date filter using the Power Bi DistinctCount() in the Power bi.

This Power Bi tutorial explained how to work with Power BI Distinct Count with a filter in Power Bi. And how to find the distinct count value with multiple filters in Power Bi. Also covered below topics:

• Power BI distinct count with filter nonblank
• Power BI distinct count with the filter all selected
• Power BI distinct count with the filter all
• Power BI distinct count without filter
• Power BI distinct count with date filter

You may like the following power bi tutorials:

• Power BI Convert Number to Text
• Power BI sort table by Date
• Dataverse Power BI
• Power BI cross filter direction
• How to create a relationship in power bi without unique values