COUNTROWS + VALUES vs DISTINCTCOUNT?

Anything related to PowerPivot and DAX Formuale
edhans
Posts: 1
Joined: Wed Jan 25, 2017 4:04 am

COUNTROWS + VALUES vs DISTINCTCOUNT?

Postby edhans » Mon Apr 16, 2018 1:11 am

I'm working my way through the Supercharge PowerBI book and in chapter 12.

What is the advantage of using a measure like:
= COUNTROWS ( VALUES (Table[Column]))
vs
= DISTINCTCOUNT (Table[Column])

For that matter, why use VALUES at all? It seems to me DISTINCT does the same thing, but the naming of it is more intuitive. What is the difference? DISTINCT and DISTINCTCOUNT seem to be a better solution, but I see VALUES used in a lot of examples, and not sure why.

MattAllington
Posts: 928
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Postby MattAllington » Mon Apr 16, 2018 6:51 am

edhans wrote:

= COUNTROWS ( VALUES (Table[Column]))
vs
= DISTINCTCOUNT (Table[Column])


There is no one right or wrong way. Sometimes one way is better but even then it is often personal preference. My book is designed to teach you reusable concepts, and in this case I was teaching you that you can create virtual tables. VALUES(Table[Column]) is such a virtual table, and to prove it i showed you that you could "countrows".

For that matter, why use VALUES at all? It seems to me DISTINCT does the same thing, but the naming of it is more intuitive.

VALUES will return any blanks as rows, DISTINCT does not. A blank will be created on the 1 side of a relationship (ie in a LOOKUP table) when there is a record in the data table with no matching record in the lookup table.

DISTINCT and DISTINCTCOUNT seem to be a better solution, but I see VALUES used in a lot of examples, and not sure why.

I think it is just personal preference, and probably what you learn. I learnt VALUES and that's what I teach.
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

Oxenskiold
Posts: 32
Joined: Tue Jan 05, 2016 10:38 pm

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Postby Oxenskiold » Fri Apr 20, 2018 6:54 pm

Hi,
VALUES() is used over DISTINCT() because it's the safe bet. It'll not hide aggregations due to the blank row phenomena as mentioned by Matt.

Most DAX functions that don't take a table expression as a parameter and at the same time return a table use VALUES() internally for the same reason: SUMMARIZECOLUMNS(), ALL(), FILTERS(), ALLSELECTED() etc.

I know of only 4 DAX functions that use DISTINCT() internally: DISTINCT(), DISTINCTCOUNT(), ALLNOBLANKROW() and RELATEDTABLE().

Best regards Jes.

MattAllington
Posts: 928
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Postby MattAllington » Sat Apr 21, 2018 7:36 am

How do you know this stuff Jes? :D
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

Oxenskiold
Posts: 32
Joined: Tue Jan 05, 2016 10:38 pm

Re: COUNTROWS + VALUES vs DISTINCTCOUNT?

Postby Oxenskiold » Sun Apr 22, 2018 12:47 am

Hi Matt, well mainly from trying to get to grips with DAX and not the least being inquisitive.

The blank row issue, the difference between VALUES() and DISTINCT() is some of the very very basic of DAX that is not at all well understood. ( It took me a while to grasp how important it actually is )

I have attached an image to pique your curiosity. It probably is a bit difficult to understand at first but the main message behind is that it only takes 4 table expressions to understand the foundation of DAX. ( I hope I'll not regret that sentence 8-) )

The 4 core DAX expressions.png
The 4 core DAX expressions.png (70.16 KiB) Viewed 40 times


It may help to remember Jeffrey Wang's blog post http://mdxdax.blogspot.dk/2011/03/logic ... table.html and in particular this excerpt.

….
In DAX, the filters in a filter context apply to following DAX table expressions:
1. A table expression that is simply a table reference, such as FactSales.
2. Values(Table[Column]).
3. Distinct(Table[Column]).
In cases of 2 and 3, the Table is filtered by filter context and then distinct values of [Column] are extracted from the filtered table.
….


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests

cron