Forum Discussion
Default filtering table with null and zero values: when click "0" it lists all 0's AND null items
How to replicate:
- This was tested on Version: 2.88.1144.0 64-bit (December 2020)
- New Power BI desktop report
- Enter data:
0 1 0 1
- 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.
- Transform data -> Add Column -> Index Column -> Close and Apply
- Create a Table visualization, drag Column 1 into Values for the Table
- Drop down Column 1 and Don't Summarize. Drop down again and click Show item's with no data
- Drag Column 1 into Values for Table a second time. Change second Column 1 to "Count". You see this:
Column 1 Count of Column 1 0 2 1 2 Total
4
- 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.
- Create a second table visualization. Drag column 1 and index into the Values. No summarization on both.
- Note how PowerBI automatically creates a "Filter" interaction between the two tables
- 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?
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
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
5 Replies
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.
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
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.
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
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
