How to filter fields so that I only have rows that are blank?

#1

I want to select all the accounts that have a specific field equal to null. Any ideas on how to do this would be greatly appreciated. Cheers

#2

You can filter the Null values using the Filter transform on a data cube. The filter transform allows you to use the Null token:Image title


You can find additional details in the article on Using the filter transform with tokens.

At the moment, this functionality is not available on the dashboard level. I opened a feature request for this and will let you know when it is implemented.

#3

Thanks Ajitkaur! An alternative way to do it I discovered was to go to the data cube, click insert other, and use the "null replacement" transform. Your reply was greatly appreciated

#4

There are a few options to tackle this. One would be at the data cube level where you can use a null replacement transform. This will allow you to provie a value to all your null accounts (i.e. "No Account" or "Blank"). Then on your dashboard you can just filter by that value. The other option, would be done entirely on the dashboard level but will require a few extra steps. You can create a formula on your visual where you can check the value of the account field and return a 0 if it's null. Then you can filter on the formula measure (i.e. return all the values that are equal to 0). The formula will look like this (note in my case I repalced the unknown values with a space):


if($Account$ != " ")
{return 1;}
else
{
return 0;
}


You can then hide the formula measure from the visual (but still filter on it). You can also setup an interaction using a button or other component to apply that filter value using a single click.

1 Like