Understanding count rows formula

Category: sql server powerpivotexcel


AlexMartini on Fri, 16 Sep 2016 02:17:46

Hello. Can someone explain how countrows works below? Thanks. 

IF( COUNTROWS( VALUES( Table1[Column] ) ) = 1, <expression> )


Darren Gosbell on Fri, 16 Sep 2016 06:41:27

starting from the inside and working out

VALUES( Table1[Column] )  returns the distinct values of Table1[Column] in the current context

So if Table1[Column] is on rows or columns of a pivot table in each cell the VALUES(Table1[Column] will return a single value. Therefore checking COUNTROWS() = 1 is a way of testing if Table1[Column] is visible on the rows or columns (or if there is only 1 item selected in a filter) of a pivot table before evaluating <expression>.

Another way of doing the same thing is

IF( HASONEVALUE( Table1[Column] ) , <expression )