topic Re: Count Rows in Visual in DAX Commands and Tips

Count Rows in Visual

BrianNeedsHelp — Thu, 24 Oct 2024 03:40:13 GMT

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?

Re: Count Rows in Visual

OwenAuger — Thu, 24 Oct 2024 05:42:47 GMT

Hi BrianNeedsHelp

You would need to do something like this:

Create a measure that constructs a table containing the same columns/measures as the matrix visual, and counts the rows.
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] ) )

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

Re: Count Rows in Visual

Jihwan_Kim — Thu, 24 Oct 2024 05:48:41 GMT

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.

COUNTROWS function (DAX) - DAX | Microsoft Learn

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

Re: Count Rows in Visual

BrianNeedsHelp — Thu, 24 Oct 2024 16:38:19 GMT

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?

Re: Count Rows in Visual

Jihwan_Kim — Fri, 25 Oct 2024 05:23:04 GMT

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

Re: Count Rows in Visual

BrianNeedsHelp — Fri, 25 Oct 2024 05:40:13 GMT

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.

Re: Count Rows in Visual

BrianNeedsHelp — Sat, 26 Oct 2024 03:33:36 GMT

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