Default filtering table with null and zero values: when click "0" it lists all 0's AND null items

[EvelynBI]

How to replicate:

1. This was tested on Version: 2.88.1144.0 64-bit (December 2020)
2. New Power BI desktop report
3. Enter data:

   1. 0
      1
      0
      1

4. edit: After entering the data you should see two steps, the Source step that input the data, and a Change type step that transforms the type of the column from "text" to "Int64". This is both necessary for my needs and for showing the problem below.
5. Transform data -> Add Column -> Index Column -> Close and Apply
6. Create a Table visualization, drag Column 1 into Values for the Table
7. Drop down Column 1 and Don't Summarize. Drop down again and click Show item's with no data
8. Drag Column 1 into Values for Table a second time. Change second Column 1 to "Count". You see this:

   1. Column 1	Count of Column 1
      0	2
      1	2
      Total	4

9. Note how Count of Column one is correct for the number of 0's, two. Also note how the count does not work for counting null items (the first row), which also gives an incorrect total count, but that's a separate question.
10. Create a second table visualization. Drag column 1 and index into the Values. No summarization on both.
11. Note how PowerBI automatically creates a "Filter" interaction between the two tables
12. Click the 0 in the first column of the first table.

And this is where the problem is. There are two zeroes, but when you click "0" in the first column of the first table, the second table shows six items, the zeroes and the nulls, like this:

Column 1	Index
	0
	2
	4
	6
0	1
0	5

Note that clicking the empty top row in the first table *does* work, the second table will only show the null items in that case. But clicking the 0 shows all null and all 0 items. When I have hundreds of nulls and one zero this behavior is a problem. Somehow the count works in the first table, it knows to count only 0's and not nulls, but the filter does not do this, it is equating 0's with nulls and showing data that does not jive with the count of 0's.

Is this behavior expected? Is there a trivial workaround? Is this a bug, and should I take this post and post it to a bug forum instead of this help forum?

[PhilipTreacy]

@EvelynBI 

Download PBIX file

This isn't the behaviour I get.  When I enter the data as you describe, I get this table

So you can see that the blank rows are correctly counted - and the total is also correct.

Your point 9. - that's what PBI does.  The filter interaction betwen tables is built in behaviour.  It's supposed to do that.

10.  When I click 0 in the first table the 2nd table is correctly filtered

So for me everything works as it should.  I'm using Dec 2020 version of PBI Desktop.  What version are you using?

Check my PBIX file linked to above and see if it works as I've decribed.

Can you supply a PBIX file with data that behaves incorrectly as you describe?

Regards

Phil

[EvelynBI]

Thank you for the reply, Phil. I put in step 9 because the filter interaction is needed for what I wanted to show, just wanted to make it explicit that the test requires the filter in case someone tests in a different way and does not setup the filter interaction.

I am also on the December 2020 version and your pbix works, but this is because your column type is "text". When I created the table it not only had the Source step to put the data in, but also automatically did a "Change type" step, = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}). With this extra step the you should notice that the "empty" items for the Text datatype turn into the italicized null keyword for the Int64 data type. I will edit the original post to indicate that the type needs to be Int64 (or likely any numeric type) for this problem to show up. In my real dataset I am running into this issue with nullable numeric columns and not "text" columns.

[PhilipTreacy · Accepted solution]

Hi @EvelynBI 

Ah, OK.  When I entered the data I don't think it did the auto type conversion. Can't quite remember.

The behaviour you are seeing is that Blank is being treated as 0, you can read more on this here

Handling BLANK in DAX - SQLBI

I created a measure to check for the count of zeroes in the table

ZeroCount = CALCULATE(COUNTROWS('Table'), FILTER('Table','Table'[Column1]=0))

and the result is 6

But if you count the number of blanks

BlankCount = CALCULATE(COUNTROWS('Table'), FILTER('Table',ISBLANK('Table'[Column1])))

the result is 4

When you filter for the value 0, the Blanks are cast/treated as 0 so you see all rows in the table that are either 0 or Blank.

When you filter for Blank, you only see Blank.  0 isn't treated as Blank.

And of course when you filter for 1 you only see 1's.

Also, if you convert the values column containing the blanks/1/0 to True/False, Blank and 0 are cast to False and 1 to True.

Download my PBIX if you want to see the measures.

Regards

Phil

[EvelynBI]

> When you filter for the value 0, the Blanks are cast/treated as 0 so you see all rows in the table that are either 0 or Blank.

Yes that's the whole issue, I found an older post where someone stated it's by design and won't be changing.

I am now using COUNTROWS instead of the built in count summarization and this properly counts blanks separate from 0's, but the issue stated in this question is still there, one visualization shows that I have two 0's, yet when I click that row to see all the rows with zeroes the other table shows zeroes and blanks. There is no solving this, there is only working around it because the underlying implementation for cross filtering uses different rules than how the table shows counts, and Microsoft has said the underlying implementation is by design.

To continue my rant, I will state again that one visualization shows one set of data, and filtering on that data results in a different set of data on other visualizations. Blank/null is not the same as 0. As programmers who use nullable types, as Power BI claims to support, we are used to setting things to null separate from setting things to 0. For example, if I'm reading temperature data every second for an entire day using a probe that only produces integer outputs due to it only being accurate to +/- one degree, I would code my program such that it produces "0" when the temperature is 0 degrees, and would produce "null" when the probe was not working for some reason. Power BI does not let me see when all the times temperature was 0, it simply does not work when there's null data. I have to manually filter using workarounds when I have 86,400 data points for a 24 hour period and faulty probes producing 10,000 nulls every day. 0 does not mean false for temperature data, I am not going to change the datatypes to handle this issue. If Power BI claims to support nulls then I am going to use them. If Power BI did NOT support nullable integer types, then yes I'd be forced to use sentinel values, "a temperature of -300 degrees was when the probe wasn't working", now all of my bar charts have to be tweaked because they extend all the way down to -300 degrees, yet when there's null data they properly just ignore the null data and there's zero extra work for me.

Manual filtering is a workaround, the underlying issue exists whether I do a separate page only filter to hide all the blanks or not. I have to give end users long winded explanations on why there's a table that shows separate counts for null data from 0 data but that they cannot do the natural thing of clicking on the 0 row to get a list of all the 0's. "Oh if you want to see the 0 data you have to get rid of all the blank data first via a separate page filter. Yes I know the dashboard works one way in all other cases, but it sometimes equates 0's and blanks (not when it's counting, though!), so here's all the instructions on how to work around that".

The simplest clarification Microsoft could make is for the filter to highlight both the 0 and the null rows when you go to filter on 0, then the end user can see, "oh I can't filter on just 0, I can see that because when I click the 0 row both the 0 and null rows are highlighted". Instead we get false information, the 0 row highlights and the null row stays grayed out, so one thinks they've now filtered to all the 0's and not the blanks, but that is misinformation.

[lbendlin]

More than likely this is expected behavior. Power BI will aggregate as much as you let it ("Don't summarize"  has a slightly different meaning as it only applies to number columns)

For #6 do a count of index instead of count of column1

For #10 you will want to refer to the way (blank) values are blended into visuals in the absence of relationships/joins.