Forum Discussion
Page Filter when Column is False or Null (from a related table)
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.
11 Replies
- themistoklis
Community Champion
Anonymous
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
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
Anonymous
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
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.
