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
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. 

5 REPLIES 5
gpsankaran
New Member

It worked for me... Thanks for posting the 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
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.