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
Sorry Anonymous
I mistyped! I should have written
This BLANK row is to represent the 1 value for which there is no corresponding row in the ColorFilter table.
So in your Color table you have the values 1 and 0 for IsPopular.
In the ColorFilter table you only have 0.
So PBI creates a BLANK row in the ColorFilter table to represent the 1 from the Color table.
That's why your slicer shows two values, Blank and 0.
When you filter on Blank it should filter the Color table as if you clicked on 1.
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.
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?
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