Forum Discussion
Filter does not work for numbers
Hi Anonymous
You have 2 tables and you created a relationship between them.
In order for this relationship to be valid/working both sides have to have the same values.
On the 1 side of the relationship you must have each distinct value from the column with the relationship - in this case IsPopular
But this is not the case so PBI creates a blank row in the ColorFilter table to represent the missing value, which is the value 1 from the Colors table.
Regarding the odd filtering behaviour, what appears to be happening is effectively filtering using Boolean true/false (or the equivalent).
Looking at your ColorFilter table with values 0, 1 and 2, when you filter on Blank, you get Yellow because the blank row is linked to the value 2 (PBI created this link/relationship because 2 is not in the ColorFilter table)
When you filter on 1 you get Blue and Green
When you filter on 0 you get all the colours that are not 1
What I think is happening is that the Blank row messes up the filtering so that 1 and 0 effectively act like Boolean True and False. In some Boolean representations True is 1 and False is any other value.
This is why filtering on 1 only gives you the colors that match 1. Filtering on 0 gives you everything else because Blank and 2 are equivalent to False.
I created a column of Boolean True/Flase and tested this and this is exactly the behaviour that occurs.
Download the file and check yourself.
I imagine the odd behaviour when you chnage the column to Text is related in a similar way.
TBH I've never looked into this in this much depth because I know I just need 1 table to create the slicer and do the filtering.
Hope this sheds some light on things
Regards
Phil
Thank you PhilipTreacy for such a detailed answer!
Unfortunately, I am still struggling with understanding the reply. What I do not understand is this:
"But clicking on 0 will show everything. It's actually filtering for 0 but because the relationship between tables is broken you see all records. The ColorFilter table needs to have 1 and 0."
What does it mean "the relation is broken"? If I change Yellow for 2 in Colors, and add 1 to ColorFilter, the relation is in the same state. But filtering starts working for 1. For 0 it still selects Blanks as well, and I cannot figure out why...
Update: also, why is it ignoring the relation state and start working when the data type changes from whole number to text?
Super UserHi Anonymous
You have 2 tables and you created a relationship between them.
In order for this relationship to be valid/working both sides have to have the same values.
On the 1 side of the relationship you must have each distinct value from the column with the relationship - in this case IsPopular
But this is not the case so PBI creates a blank row in the ColorFilter table to represent the missing value, which is the value 1 from the Colors table.
Regarding the odd filtering behaviour, what appears to be happening is effectively filtering using Boolean true/false (or the equivalent).
Looking at your ColorFilter table with values 0, 1 and 2, when you filter on Blank, you get Yellow because the blank row is linked to the value 2 (PBI created this link/relationship because 2 is not in the ColorFilter table)
When you filter on 1 you get Blue and Green
When you filter on 0 you get all the colours that are not 1
What I think is happening is that the Blank row messes up the filtering so that 1 and 0 effectively act like Boolean True and False. In some Boolean representations True is 1 and False is any other value.
This is why filtering on 1 only gives you the colors that match 1. Filtering on 0 gives you everything else because Blank and 2 are equivalent to False.
I created a column of Boolean True/Flase and tested this and this is exactly the behaviour that occurs.
Download the file and check yourself.
I imagine the odd behaviour when you chnage the column to Text is related in a similar way.
TBH I've never looked into this in this much depth because I know I just need 1 table to create the slicer and do the filtering.
Hope this sheds some light on things
Regards
Phil