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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
BrianNeedsHelp
Helper III
Helper III

Count Rows in Visual

I want to count rows in a Matrix visual.  The row count would change based on what is selected in the slicers.   I want to put the count in a card that will change dynamically based on what is selected in the slicers.  The categories I have are Location and # of Sales.  

I tried Count(Location), but I keep getting the error "parameter is not the correct type".  I tried CountRows, which asks for the table.  What table?   Location comes from one table and # Sales comes from another table!  I'm new to this, but in Excel it's SO EASY.  How can I make this work please?  

1 ACCEPTED SOLUTION

Hi,

I am not sure if I understood your question correctly, but I think we can try using numeric parameter something like below. Please check the below picture and the attached pbix file.
Or, please share your sample pbix file's link to help me to have a bit more clear understanding.

 

Use parameters to visualize variables - Power BI | Microsoft Learn

 

 

Jihwan_Kim_0-1729833630459.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

6 REPLIES 6
BrianNeedsHelp
Helper III
Helper III

It works for the Slicers when I just use Location.  When I try to add Sales it says "Parameter is not the correct type".   But it works like I had wanted, but I forgot to mention that I used a filter for Sales Amount.  So the whole idea is to count location where the Sales Amount is less than X amount.  So it ignores that when it's counting the rows-it correctly counts only what's selected in the slicer.  So any idea on that part?  

Hi,

I am not sure if I understood your question correctly, but I think we can try using numeric parameter something like below. Please check the below picture and the attached pbix file.
Or, please share your sample pbix file's link to help me to have a bit more clear understanding.

 

Use parameters to visualize variables - Power BI | Microsoft Learn

 

 

Jihwan_Kim_0-1729833630459.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

This works amazing!  Thank you so much for the PBIX.   I was able to implement it.  

Thank you.  I'll look at the Pbix as soon as I can.  I was meaning that let's say there are a 100 rows in table.  If I use the slicer to select a filter, it correctly counts the rows e.g. 75 rows.  But on the filter pane I have selected to show sales less than x amount.  So in the visual it counts 75 rows but should only count 50 rows.  I think that's how you have it displayed but just wanted to explain.  

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

In the measure, I used COUNTROWS DAX function.

 

Jihwan_Kim_1-1729748829507.png

 

 

Jihwan_Kim_0-1729748800228.png

 

COUNTROWS function (DAX) - DAX | Microsoft Learn

 

Location count: =
VAR _t =
    SUMMARIZE (
        sales,
        location[location]
    )
RETURN
    COUNTROWS ( _t )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

OwenAuger
Super User
Super User

Hi @BrianNeedsHelp 

You would need to do something like this:

  1. Create a measure that constructs a table containing the same columns/measures as the matrix visual, and counts the rows.
  2. Place that measure on a card visual and ensure the same slicers are filtering both the matrix and card. 

Explanation:

  • Matrix visuals generate queries using SUMMARIZECOLUMNS behind the scenes, which you can capture and examine using Performance Analyzer (see here).
  • With default settings, the number of rows in a matrix (and returned by SUMMARIZECOLUMNS) depends on the combinations of row fields for which at least one measure is nonblank in at least one column.
  • To create your row count measure, you can take the SUMMARIZECOLUMNS component of the query and simplify/modify to produce the number of rows you actually need, depending whether you want to include subtotals etc. Then count the rows of the resulting table.

Simple example (PBIX attached):

  • Assume we have a matrix containing Country and Brand on rows, and Sales Amount as the only measure.
  • The matrix does not include subtotals or grand total.
  • We can then create this measure to count the rows and display on a card visual:
Matrix Row Count - Country|Brand|Sales Amount = 
COUNTROWS (
    SUMMARIZECOLUMNS (
        'Product'[Brand],
        Customer[Country],
        "Sales Amount", [Sales Amount]
    )
)

OwenAuger_0-1729748381384.png

Notes:

  • The measure assumes specific row/column/measure fields are used in the visual.
  • This would need to be modified if subtotal rows are to be included or if fields are placed in columns rather than rows.

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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