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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Alice_BI
Frequent Visitor

How to count rows in a Power BI table visual with multiple filters?

hi guys, Please please Help 

 

I have a seemingly easy problem that takes one click to fix in Excel but in Power BI this is unnecessarily complicated.

 

I have a simple Power BI table visual that contains columns from multiple tables. It's got Customer name, address, sales quantity, sales volume and sale, shipment dates. I have several filters in the filter pane + date slicer. I just need a count of rows in that table to show up in a card above it. I tried Count(Sales fact[Sales qty]), countrows(Sales fact), Calculate(count(Sales fact[Sales Qty]), filter 1, filter 2, filter 3) etc. They all yield the same incorrect result. To simplify, on Sept 1 there were 8 sales quantities - the card says 8 but the table clearly shows 10 rows. That's because some clients have more than 1 address. In this case one client had 3 addresses so that line is tripled givinng a total row count of 10.  I need to see all addresses and for the card to show 10 not 8. - Basically to show the exact count of rows on the visual, no more no less. None of the Counta, countx, counrows forumas pick up those duplicate rows... I just need a simple cpunt of rows that i see  like in Excel. Please please help. 

6 REPLIES 6
gpsankaran
Regular Visitor

It worked for me... Thanks for posting the solution.

Where is the posted solution?

YukiK
Impactful Individual
Impactful Individual

I've done this at work once. It's a lot more work than it seems. You need to create a virtual table that contains any table/column and do COUNTROWS() over it.

Something along with this will work:

Your Measure = 
COUNTROWS(
    SUMMARIZE(
        Sales,
        Customer[name],
        Customer[Address],
        etc
    )
)

Thank you Yukik! 

I'm almost there and i found the culprit: 

Coutrows(Summarize(Sales Fact, Custumer Dim[Name],Business Dim[Name],Orders Dim[ID] etc.. ))

 

This almost works but im missing 2 rows  , i couldn't include the Client address column in the Summarize table above because it's a snowflake dimension from the main client dimension..  How do i include it?

 

YukiK
Impactful Individual
Impactful Individual

That should. Assuming those dim tables have a relationship the fact table. At least this is how I got around it I believe. It's basically trying to create the same DAX code as the visual produces if that makes sense

One of the fields from the client addresses dimension included in the table visual does NOT have a relationship to the fact table. It's a snowflake from another dimension. That's why im missing 2 records. Any idea how to include it?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.