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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hksl
Helper I
Helper I

Total number of rows in the table visualization with 3 columns

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

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

Sure, it's:

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

17 REPLIES 17
BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




'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
Community Champion
Community Champion

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]
	)
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

Sure, it's:

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This was really helpful. Thanks

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

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ok, understood. Many thanks again for all your help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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