Forum Discussion
Agg-tables - What is the difference between Count and Count Rows, and can you use distinctcount?
When you mark a table as an aggregate table in Power BI, you can map columns in the aggregate table to the details-table using summary functions like: Count, GroupBy, Max, Min, Sum and Count Rows.
I do understand that Count Rows is a count of all the rows making up the summary row in the aggregate table.
But, what is Count? A DAX COUNT of all the rows containing values? How does this work with blank or empty strings? As per usual DAX?
Something that would be very usefull is a summary function like DISTINCTCOUNT. Is this possible to achieve with PowerBI as-is, or is it a missing feature? Other summarization fuctions like COUNTX, AVG, ... would be great to see as well.
Hi Toerstad
CountRows->calculate the rows of a table
Count->calculate the rows of a column
DISTINCTCOUNT->calculate the distinct rows of a column
They all ignore the blank or empty strings.
COUNTA would consider blank or empty strings.
COUNTX, COUNTAX can calculate for measures.
You could learn more here:
A video: https://www.youtube.com/watch?v=V8wYTjKyvgk
DAX Reference:
https://docs.microsoft.com/en-us/dax/count-function-dax
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Toerstad
CountRows->calculate the rows of a table
Count->calculate the rows of a column
DISTINCTCOUNT->calculate the distinct rows of a column
They all ignore the blank or empty strings.
COUNTA would consider blank or empty strings.
COUNTX, COUNTAX can calculate for measures.
You could learn more here:
A video: https://www.youtube.com/watch?v=V8wYTjKyvgk
DAX Reference:
https://docs.microsoft.com/en-us/dax/count-function-dax
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi.
Thank you for your reply.
I have a fairly decent grip on how the regular DAX expressions work, but I feel how this is implemented in PowerBI aggregation tables are poorly documented on docs.
I assume they work the same way as per usual DAX, but this is an assumption.
As I mentioned, I would also like to see other summarization functions like DISTINCTCOUNT, is there any support for this? Will there be?
As i write this I realize that a COUNTROWS on an aggreagate table GroupedBy by the column i want to DISTINCTCOUNT might be an option. But – then this would bloat my PBIX due to the high cardinality of my column.
Hi Toerstad
Could you show an detailed example?
There are some references:
https://docs.microsoft.com/en-us/dax/distinctcount-function-dax
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
