cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vp_powerbi
Helper I
Helper I

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);

image.png

1 ACCEPTED SOLUTION

Hi @vp_powerbi 

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

inv-rel.png

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)

filt-bl.png

 

When you filter on 1 you get Blue and Green

filt1.png

 

When you filter on 0 you get all the colours that are not 1

filttf.png

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.  

ftf.png

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

8 REPLIES 8
PhilipTreacy
Super User III
Super User III

Hi @vp_powerbi 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

Sorry @vp_powerbi 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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...image.png

 

Update: also, why is it ignoring the relation state and start working when the data type changes from whole number to text?  

Hi @vp_powerbi 

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

inv-rel.png

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)

filt-bl.png

 

When you filter on 1 you get Blue and Green

filt1.png

 

When you filter on 0 you get all the colours that are not 1

filttf.png

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.  

ftf.png

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

PhilipTreacy
Super User III
Super User III

Hi @vp_powerbi 

Download this sample PBIX with data and the following visual/slicer

You don't need 2 tables. Just use the Colors table and create a slicer that uses IsPopular.

 

Data with no filter

nofilter.png

 

IsPopular

ispop.png

 

Not Popular

notpop.png

 

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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 🙂

Hello @vp_powerbi 
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.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.