cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soldstatic Regular Visitor
Regular Visitor

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 11
themistoklis New Contributor
New Contributor

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

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

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

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 New Contributor
New Contributor

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

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

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

Sure!

 

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

 

Job DateJob TypeCustomers
1/1/2018A49
1/2/2018B3
2/23/2018C27
3/15/2018A3
3/16/2018B20
4/3/2018B10
6/4/2018D14
8/19/2018C8
8/19/2018B36
9/19/2018A36

 

 

 

Want the visual to show:
Job DateJob TypeCustomers
1/1/2018A49
2/23/2018C27
3/15/2018A3
3/16/2018B20
4/3/2018B10
6/4/2018D14
9/19/2018A36

 

 

themistoklis New Contributor
New Contributor

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

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

image.png

soldstatic Regular Visitor
Regular Visitor

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

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 New Contributor
New Contributor

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

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

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

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 New Contributor
New Contributor

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

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