Forum Discussion

Alice_BI's avatar
Alice_BI
Frequent Visitor
3 years ago

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. 

Need Help
Tips and Tricks

6 Replies

  • YukiK's avatar
    YukiK
    Impactful Individual
    3 years ago

    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
    )
)
    • Alice_BI's avatar
      Alice_BI
      Frequent Visitor
      3 years ago

      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's avatar
        YukiK
        Impactful Individual
        3 years ago

        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