Agg-tables - What is the difference between Count and Count Rows, and can you use distinctcount?

[Toerstad]

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.

[v-juanli-msft · Accepted solution]

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
Maggie

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

[Toerstad]

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.

[v-juanli-msft]

Hi @Toerstad 

Could you show an detailed example?

There are some references:

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

https://stackoverflow.com/questions/50414271/creating-a-measure-that-returns-distinct-count-by-count...

Best Regards
Maggie

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