Total number of rows in the table visualization with 3 columns

[hksl]

I am new to Power BI. Using free download of  PBI Desktop. I have created table visual with 3 columns "Location_Nb", "SubLoc_Nb", "SubLoc Group Nb".  Is it possible to add row to this visualization : "Total number of rows" which would display total number of rows in this visualization?
Or count total number of rows in this visualization and display it in the Card visualization, for example?
I've read some posts, found some DAX syntax, but Total is a wrong number. "MyTable" is actual table used to create visuals.
Total should be 1250, but my DAX shows 89.
Here is my measure for Card visual:
Measure = COUNTROWS (filter(SUMMARIZE ('MyTable',[Location_Nb],"Row Count", 'MyTable'[Location_Nb]), [Row Count]>0))

Thank you

[BhaveshPatel]

Total Rows:=COUNTROWS(tablename)

You do not need to write complex formulas. The above formula will calculate the total no. of rows in your table. Just right click your table, select create measure and write the above DAX formula( Replace tablename with your tablename) and it will give you results explected which you can use in your card visual.

[hksl]

Thank you BhaveshPatel but I need total nb of rows in Visualization of type "table", not in original table. Rows in "table visualisation" are result of me adding some fields from "dataset table" and applying filters. 

[KHorseman]

The COUNTROWS formula will count rows in the filtering context it's placed into. So if it's placed into a table visual that has some filters applied to the base table that it's counting, it will only count the resulting filtered rows.

[hksl]

I have set Measure = COUNTROWS ('MyTable'). But now, in Card visualization,  it shows 4M (with Decimal Units = Auto) or 3884014 (with Decimal Units = none.) Whicg is total number of rows in Dataset table. 

There are only 1250 rows in the 'table visualization' with filters applied.  So, how to count those?

I am using Card visualization along with 'table visualization' becaus eI do not know how to add 'total nb of rows'  row to the 'table visualization'.

Thank you

[KHorseman]

If those filters are only on the table visual then they will only apply to the table. You would have to apply the same filters to the card, or to the entire page.

[hksl]

Thanks again. I've applied 'page level' filters, it did not allow me to apply to card visual. I've changed filter to be sure of nb of records in 'tbl visual'. I can see that there are 26 rows in 'tbl visual', but Measure = COUNTROWS ('MyTable') shows me 269. 

Applying report level filters did not help.    

So, I have three columns: "C1", "C2", "C3".

'table visual' shows 26 rows for when C1 = 96 (filter). It seems some grouping of rows happens?

Measure = COUNTROWS ('MyTable') shows 269 - which is total nb of rows in original db table for C1 = 96

It feels like I need measure that reflects grouping applied to 'tbl visual'  ?

[KHorseman]

Are you just showing raw columns in your table visual or are you using measures there? If the only filter is C1 = 96 and you're just showing columns, it should show all the rows where C1 = 96 in the table visual. You say there are 269 rows in your table where C1 = 96 so why is your table visual not showing 269 rows?

[hksl]

'tbl visual' - to created it I've drag/dropped C1, then C2, then C3.  I original table each C1 value has multiple C2 values associated with it. And each C2 value has multiple C3 values associated with it.  It feels that somekind of grouping happens when 

in the "Visualizations" pane :

"Values" shows C1, C2, C3.

"Filters" shows C1 is 96; C2 - no filtering value selected; C3 - no filtering value selected;

-----

I was able to reproduce in SQL how only 26 rows produced:

select MAX(C1), C2, C3
from MyTable
where C1= 96
group by C2, C3
order by C2, C3

It looks like all this logic was reated just by me adding columns to the 'tbl visual'

I did Not specify anywhere 'group by' conditions.

But this logic does not apply to 'card visual'

[KHorseman]

That makes a big difference. If there's grouping between the columns then you probably need to write a measure that replicates the table visual. You were on the right track by using SUMMARIZE. I think it will be something like:

Measure = COUNTROWS(
	SUMMARIZE(
		FILTER(
			TableName,
			TableName[C1] = 96
		),
		TableName[C1],
		TableName[C2],
		TableName[C3]
	)
)

[hksl]

Many thanks, this works, returns 26! One last question: I did Not add any grouping, so it looks like PBI creates some grouping based on the nature of the data ?

[KHorseman]

Yes, that's how a matrix works. You have a natural hierarchy of data that can be summarized. The formula I suggested and the matrix do the same thing.

[hksl]

Many thanks again. May be there is way to convert "summary of natural hierarchy of data" implemented for 'tbl visual' into DAX statement?  Or extract that 'query' i some other way? To make it easier to apply to visualizations.

Thank you

[KHorseman · Accepted solution]

Sure, it's:

	SUMMARIZE(
		FILTER(
			TableName,
			TableName[C1] = 96
		),
		TableName[C1],
		TableName[C2],
		TableName[C3]
	)

[Anonymous]

This was really helpful. Thanks

[hksl]

I mean not-to-write Dax query, but after 'tbl visualization' is created by drag/drop -> generate query using existing 'tbl visualization'.

[KHorseman]

I have no idea what you're asking for. A visual is just a visual. It doesn't generate any query that you can access.

[hksl]

Ok, understood. Many thanks again for all your help