Count number of rows in visual

[pabeader]

I have a table visual on my page.  I want to know the number of rows that appear in the visual.   There are slicers, visual level filters, page level filter, and report level filters, all acting on the table.

With NO filters at all the number of visible rows is 342565.  

With ALL the different filters the number of visible rows is 4.

I would like a measure that provides the result 4 or 245 or 342565 or whatever the number rows are visiable in the table visual.

result  = COUNTROWS(Table)  does not work.

[ramdasanudas]

I also had the same problem in an already created report

I just wanted to know the count for myself

and when I saw the Filters pane, It had 2 Page level filters Year and Month, when I was a selecting a month --> it was displaying a count in the year filter. 

[pabeader]

I posted a visual that shows what I'm doing.  I hope somebody can figure out the DAX for this.  So far I've tried every trick on Google that I can find and nothing has worked.

[pabeader]

This should have a result of 2.

[OwenAuger · Accepted solution]

Hi again @pabeader 

Thanks for posting the image - apologies I was a bit busy last few days.

It looks like all the fields in the table are columns rather than measures - is that correct?

You can try a measure like this, replacing YourTable with the actual table name(s):

Table Row Count = 
COUNTROWS ( 
    SUMMARIZE ( 
        YourTable,
        YourTable[Carton#],
        YourTable[Cartoned time(STD)],
        YourTable[Size],
        YourTable[Item#],
        YourTable[Joe#],
        YourTable[MaryAnn#],
        YourTable[TO]
    )
)

This measure creates a table of visible distinct combinations of the listed fields. In this case I'm expecting it to produce the same set of combinations generated by SUMMARIZECOLUMNS in the visual's DAX query.

You could possibly shorten the list of arguments within SUMMARIZE to just those that define the lowest level of granularity.

Regards,

Owen

[pabeader]

Thank you for this.  I already tried something similar from the previous response.  It only gives the total nunber rows.  Not the number of selected rows that are visible in the visual.  I'm using this in a card and I think that might be the part that folks are missing.  This isn't something that is in the visual itself but off to the side.

I have found a way to make it work, though.  If I put filters on the page, then the card, with this measure in it, shows the number of rows but that is obviously what it would do.  Not what I was trying to do but it should be okay for the report.

[OwenAuger]

Thanks, Ah I understand your point after looking at your screenshot again.

I was assuming that the report was set up so that the same filters applying to the table would apply to the card.  But this is not possible with visual-level filters on the table, since visual-level filters are not visible to any other visual.

The only solution is to have filters outside both visuals that apply to both (slicers, page-level or report-level) which it sounds like you've done.

[OwenAuger]

Hi @pabeader 

The number of rows in the table visual is determined by the DAX query generated by the visual, which will include a call to the SUMMARIZECOLUMNS function.

One method could be to count the rows of the SUMMARIZECOLUMNS(...) function called in the DAX query generated by the visual.

You can inspect the query generated using Performance Analyzer and copying the DAX query. Then isolate the SUMMARIZECOLUMNS(...) portion of the code, and create a new measure that looks like:

Table Row Count = 
COUNTROWS (
    SUMMARIZECOLUMNS(
      'Table1'[Col1],
      'Table2'[Col2],
      "Measure 1", 'Table3'[Measure1]
      "Measure 2", 'Table4'[Measure1]
    )
)

There are cases where such a measure will return an error (SUMMARIZECOLUMNs is not always usable in measures), so as an alternative you could try to build the same table created by SUMMARIZECOLUMNS using other table functions such as SUMMARIZE.

Please post back if needed - some details of the data model and fields included in the table may help.

Regards,

Owen