COUNTROWS(ALL([Table]) calculation error

[SanderVeeken]

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.

[Anonymous]

Hi @SanderVeeken,

Can you please create some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[SanderVeeken]

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.

[Anonymous]

Hi @SanderVeeken ,

If you want to further check and trace with data model tables, you can try to use third party tools or SSMS to connect to power bi data model(AS Tabular).

External tools in Power BI Desktop - Power BI | Microsoft Learn

Connect to Power BI Desktop Model from Excel and SSMS (biinsight.com)

Regards,

Xiaoxin Sheng