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.
In your sql query for the flag field change the null values to TRUE (or to another preferred value) using a case statement
CASE WHEN s.flag IS NULL THEN 'TRUE' ELSE s.flag END AS s.flag
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. :/
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
|Bad Dates||Exclude Flag|
|Job Date||Job Type||Customers|
|Want the visual to show:|
|Job Date||Job Type||Customers|
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.
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.
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.
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.