Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter does not work for numbers

Problem: when I filter a table via a slicer, filtering is not working. I have no measures, no calculations, just two tables with a relationship set.

 

Details:

I have two tables imported from SQL Server.

The first table is Colors. It has two columns: ColorName (=VARCHAR) and IsPopular (=INTEGER). IsPopular can have only 0 or 1 here.

The second table is ColorFilter. It has one column only: IsPopular (=INTEGER), and it has only one row with 0 as its value. This table is going to be used to filter the ColorName table.

 

In PBI model there is a relationship between these tables by the IsPopular column (one ColorFilter to many Colors). The data type of the IsPopular columns is WholeNumber.

 

I have two visuals in the report: a table and a slicer. The table displays the Color data, and the slicer is based on the ColorFilter table.

 

When I select 0 in the slicer, the colors are not filtered. When I change the data type of the ColorFilter.IsPopular to Text, the colors start getting filtered.

 

I would appreciate if you tell me why this is happening, please.

 

CREATE TABLE Color(
ColorName VARCHAR(100) NOT NULL,
IsPopular INTEGER NOT NULL);

 

CREATE TABLE ColorFilter(
IsPopular INTEGER NOT NULL);

 

INSERT INTO Color VALUES('Green', 1),('Blue', 1),('Yellow', 1),('Red', 0);

INSERT INTO ColorFilter VALUES(0);

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

     

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Philip,

       

      Thanks for answering.

      Yep, I know I can go this way. What I do not understand is why the original setup works in that manner. That was the question 🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello Anonymous 
        This is happening because of a relationship. You have one to many relationships and if you change to many to many it'll work as expected.

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    In the ColorFilter table because it only contains a 0, PBI creates another BLANK row because the Color table contains 1 and 0.  The table on the 1 side of a 1-M relationship must have every value that appears on the M side.

    This BLANK row is to represent the 1 value for which there is no corresponding row in the ColorFilter table.

    If you add the value 1 to the ColorFilter table the slicer will work as expected.

     

    Refer to this for further info

    Model relationships in Power BI Desktop - Power BI | Microsoft Docs

     

    Regards

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Philip,

       

      "This BLANK row is to represent the 0 value for which there is no corresponding row in the ColorFilter table."

       

      Did I understand it correctly that 0 as a number is the same as Blank?

      Thanks.

      • PhilipTreacy's avatar
        PhilipTreacy
        Super User
        5 years ago

        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