Forum Discussion

SanderVeeken's avatar
SanderVeeken
Helper III
3 years ago

COUNTROWS(ALL([Table]) calculation error

Hi everyone,

 

I have a weird one. I'm doing a very simple measure to switch some text output depending on whether "all" values are selected or a subset it selected. It looks like this, basically:

VAR Partner_Count=COUNTROWS(Partners)

VAR Partner_Count_AllPossible=COUNTROWS(All_Partners)

RETURN

SWITCH(
    Partner_Count,

        0, "",

        1, SELECTEDVALUE(Partners[Name]),

        Partner_Count_AllPossible, "All Partners")

 

This omits the possibility that multiple partners are selected but that's not part of the error (I accounted for that in my formula) - my problem is that COUNTROWS(Partners) gives me 25 and COUNTROWS(ALL(Partners)) gives me 26, even when no filters are active. That means that the "all partners" option is never selected by the Switch. When I look at the table, I  clearly see it has 25 rows (and a header row). Is it possible that COUNTROWS(ALL... etc. somehow counts the header? Or where else could the "extra" value be coming from?

Unfortunately the source data is from AAS and covered by an NDA so I can't share it.

Need Help

3 Replies

    • SanderVeeken's avatar
      SanderVeeken
      Helper III
      3 years ago

      I can't, because obviously with dummy data the problem doesn't occur. What I was wondering is what might be wrong with the data to give this result.

       

      It turns out, after talking with the DWH team, that there is a problem in the logic where blank rows are added to the tables. So until they fix this, instead of doing "countrows(all....etc." I'll use ALLNOBLANKROW.

       

      What's strange/annoying is that I can't seem to see this anywhere by looking at the data on the PowerBI side. The table view is unavailable for Live Connection or DirectQuery. So all I can do is create a table visual and throw everything in there - and there it doesn't show up! A simple countrows even gives me 25 rows. It's only when I do countrows(all... that I get 26 as a result.

       

      So this just as a heads up if someone might be in a similar situation. I hope that they will make it somehow possible to look at the "real" table in a DirectQuery/LiveConnection situation.