locked
When does DAX Countrows() returns a 0 as a value instead of "blank" RRS feed

  • Question

  • I was suprised to find the combination "..ISBLANK(Countrows(filter(...." in a measure, as I assumed controws() would return 0 if there are no rows, so this would always make isblank return True.

    I looked it up in the MSDN function reference for Dax, and there it is stated that:

    Whenever there are no rows to aggregate, the function returns a blank. 

    This I had figured out myself, however I do not understand the next bit.

    However, if there are rows, but none of them meet the specified criteria, the function returns 0.

    What are "specified criteria"? I tried different combination of calculate and filter and countrows, but it always returns a blank value in the pivottable.

    Carsten

    P.S. Similar sentences appear in the Reference articles of other Count-functions, but I still do not see what it is supposed to mean.


    Monday, March 18, 2013 8:37 PM

Answers

  • Well, I am not sure. I just re-read the Reference article (has it changed?).

    This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table.

    Whenever there are no rows to aggregate, the function returns a blank.  However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions.

    That seems to imply hat when countrows(filter(T,...)) in case where T already has no rows, blank is returned, and in case T has rows, but all of them are filtered out by ... , then 0 is returned?!?

    I'll have to check if that is the case..


    Monday, November 25, 2013 9:02 PM

All replies

  • ElPresidente,

    Is this still an issue?

    Thank you!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 24, 2013 2:19 AM
  • Well, I am not sure. I just re-read the Reference article (has it changed?).

    This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table.

    Whenever there are no rows to aggregate, the function returns a blank.  However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions.

    That seems to imply hat when countrows(filter(T,...)) in case where T already has no rows, blank is returned, and in case T has rows, but all of them are filtered out by ... , then 0 is returned?!?

    I'll have to check if that is the case..


    Monday, November 25, 2013 9:02 PM