Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @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

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
Super User

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



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!


Anonymous
Not applicable

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 @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



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!


Anonymous
Not applicable

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 @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

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!


PhilipTreacy
Super User
Super User

Hi @Anonymous 

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!


Anonymous
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.