How to count rows with filter in Power BI tables?

by

Here’s a question from Harry:

I have a table with sales data. I would like to create a Power Bi report that will show the count of all specific weeks in which the order intake exceeded a specific amount. Any ideas on how to proceed here.

Power BI rows counting with DAX and visuals – Example

Step 1:Prepare your data table

First off, you will need to identify our data. In this short tutorial we will work with the Sales by Month and Week table. Here is a very simple snapshot of our table:

This is just a quick snapshot of the table. We can easily get the table row by using simple measure.

  • In the Data View, select your table.
  • Hit the New Measure button.
  • Type the following DAX statement:
Number of rows = COUNTROWS ('Sales by Month and Week')

The table contains 44 records. We can easily visualize the measure that we just created in a visual Card.

  • In Power BI, open the report View.
  • Add a new page to your report.
  • From the Visualization pane, drag a Card visual into the canvas.
  • Then drag the Number of Rows measure onto the Card.
  • Here’s the result:

Step 2: Create a DAX calculated measure to filter your data

In the previous step we visualized the overall number of rows. That said, we need to display only the row count of weeks that exceeded a certain KPI threshold. Let’s assume that we will show only weeks in which sales exceeded 10K.

To support that we will use the CALCULATE function, which allows to apply a filter when calculating a measure. We can use the Number of Rows measure we defined earlier and add a condition that will display only records in which the sales amount is greater than 10K.

Weeks_over_10K = CALCULATE([Number of rows], 'Sales by Month and Week'[Sales Amount]>10000)

Step 3: Create your visual report to display filtered rows

Back to the Report View, we will now go ahead and complete our report:

  • Drag the following visuals into the canvas: Table, Slicer and two cards.
  • Display your table row in the table.
  • Next drag the two cards into the canvas.
  • Link the Number of Rows measure to one and the Weeks_over_10K one to the second car.
  • Add a slicer and drag the Month variable to it.
  • Optionally change the canvas background and modify your visual aesthetics as needed.

Here’s the end result: