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