Page Filter when Column is False or Null (from a related table)

[soldstatic]

I have a table of some dates with a flag based on the severity of overall system issues on that date. There are only entries for some dates where system wide issues happened, and only some of those entries have the flag set. 

I am trying to analyze another table which ~300 rows of data per day at minimum. I want to filter out the dates with the true flag, but leave the dates with the false flag and also leave dates where there are no entries in the related table. If I were to use SQL I there where clause would be akin to this:

from j, s
where (s.flag=True or s.flag is null)
AND j.date=s.date(+)

Using the page level filter I can filter on 'is not true', and I can type 'Null' in an the clause but it doesn't work. 

[vliet_pbi]

There is a small but very important difference between DAX and SQL:

NULL is not equal to anything else, even NULL = NULL is not true in SQL.

BLANK() is equal to 0, "" and FALSE(); yes, that is not what one would expect, welcome to DAX!

The way filters work in Power BI makes them useless for Boolean columns with blanks.

Although they are shown separately, you cannot select them separately:

(Blank) will also select False, and False will also select (Blank), always!

And there is no advanced filter available on these columns to include or exclude blanks.

To avoid this confusion for our report builders, today we decided to convert all bit columns from SQL (that will become Boolean columns in Power BI) to varchar columns returning 'Yes' or 'No'. That is quite an operation but faced with the problems when using Booleans in filters we did not have any other choice.

I hope this explains the problems you are facing: one cannot filter rows containing either True or (Blank) in Power BI, and finding out why this does not work is not easy. There are articles from SQLBI about issues surrounding BLANK() in DAX formulas, but they do not mention this filter issue in Power BI.

[themistoklis]

@soldstatic

In your sql query for the flag field change the null values to TRUE (or to another preferred value) using a case statement

e.g. 

CASE WHEN s.flag IS NULL THEN 'TRUE' ELSE s.flag END AS s.flag

[soldstatic]

Hey Themistoklis,

Thanks for your reply!

I'm not actually using sql here, just using what I"m trying to do as an example. I would prefer to leave the tables and data structure alone as much as possible to maintain the original structure from the core application whose data we are analyzing. The SQL I mentioned was just the example, if I used what I posted it would work as written basically. But how do I get the same thing without making it a custom query and changing up how the data is used?

I can set a filter to show items when the flag is false and add the 'or' criteria of is blank, or I can manually type 'null' in there and try that, but it always filters down onto ONLY the false rows. So rows where there isn't a date match are excluded. 😕

[themistoklis]

@soldstatic

Could you please provide us with a sample dataset so as to work on your issue?

Just dummy data and mask anything that is sensitive data

[soldstatic]

Sure!

Bad Dates	Exclude Flag
1/1/2018	FALSE
1/2/2018	TRUE
6/4/2018	FALSE
8/19/2018	TRUE

Job Date	Job Type	Customers
1/1/2018	A	49
1/2/2018	B	3
2/23/2018	C	27
3/15/2018	A	3
3/16/2018	B	20
4/3/2018	B	10
6/4/2018	D	14
8/19/2018	C	8
8/19/2018	B	36
9/19/2018	A	36

Want the visual to show:
Job Date	Job Type	Customers
1/1/2018	A	49
2/23/2018	C	27
3/15/2018	A	3
3/16/2018	B	20
4/3/2018	B	10
6/4/2018	D	14
9/19/2018	A	36

[themistoklis]

@soldstatic

Please see attached power bi workspace based on the information your provided to me.

I have added the data and on Visual Level Filters i selected 'False'. This did the trick. See image below:

[soldstatic]

Well when I do this it filters on ONLY the rows with 'FALSE'. I downloaded your pbix and it works but I don't understand why yours is showing blank / null rows, when the filter only has FALSE selected. Additionally I changed the cross filter type in yours from many to one to many to many, which is forced in my file for some reason (I'm guessing because it is in direct query mode). This creates the same behavior I have, where it shows only the rows with false.

What I don't get is why your method below even works. I would assume that in any mode, if you selected a single flag you would only want to see that flag and not the null/blanks. If you wanted the inverse of the data set we've been talking about (show only the count of customers for where the flag is true) you would have to select the true filter, and then add a 'not blank' filter I guess? That isn't how I would normally expect it to work. 

[themistoklis]

@soldstatic

The existence of NULL values in the filter is also a matter of how the data schema has been created.

I just created a report based on the data you provided in the message. I just put them in an Excel spreadsheet and loaded them to the report.

Now why yours has different behaviour from mine, i dont really know, because i havent seen your report.

If you could share it with us then we will be able to tell you what's happeining.

Also make sure you have the latest version of the PowerBI Desktop installed on your machine.

[soldstatic]

Yea I'm not sure if it's a direct query thing or what.

Let's take it from this angle. Using the same type of PBIX you uploaded, I'm getting the same behavior with manually entered data. Now, how would I get it to ONLY show records where there was a match and the flag was false? IE, I only want to see Jan 1 and June 4 data from the second table.

[themistoklis]

@soldstatic

As mentioned it highly depends on how you have the data schema.

If you want to see these 2 dates then you need to add Bad Dates on a table object and on Filter select False.

If you add Job Dates and the filter will be False then you will also get the row with blank values.

That's because it is a many to one relationship from Dataset 2 to Dataset 1 and Dataset 2 is a master dataset.

I hope it makes sense.

[soldstatic]

I guess I'm not picking up what you're putting down. If I make a table visualization with the Bad Dates and Customers as the only two fields being used it shows the same thing (if you look at the total sum of customers) as if I had Job Date and customers. 

Basically I don't understand this behavior. If I select "False" it filters on False (and includes Job Date records that are blank/don't have the match). if I select "True" it only shows trues. These seems like inconsistent behavior and doesn't make sense to me.

In my other file that uses direct query, selecting "False" shows only the false records, and selecting "True" shows only the true records, and there is no way that I can see to include the nulls / blanks that don't have matching records. If I go to Advanced filter method, I can enter to "Show items when the value is not True", but that only shows the False records and doesn't allow the Nulls / Blanks to show up.

So the behavior with direct query doesn't match the behavior with data entered directly, and neither method give me the ability to consistently handle the nulls.