March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
It worked for me... Thanks for posting the solution.
Where is the posted solution?
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?
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
86 | |
83 | |
72 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |