Slicer based on relationships - how to change name of the option called (blank)

[gittelena]

Hi there,

Hope you can help me understand how to change name of the option called "(blank)" in my slicer (and chart) - see picture below.

Here is some background:

•  I want to show - and be able to filter on - how many of the customers from my master query are preferred customers.  
•  My slicer and chart pulls in data from 2 different queries. I have connected the two via a relationship (cardinality = "Many to one", cross filter direction = "Single")
•  My master query contains a list of customers and a unique customer id. My other query contains a list of customers who are preferred customers and their unique customer id. I have connected these two queries with the unique customer id.
•  My slicer and chart now shows those that are on the list by showing the field "Yes" (which is a column in the preferred customer query, but then for those that it cannot match it shows "(Blank)". Is way I can change the name from "(Blank)" to e.g. "No"?

Thanks!

GL

[v-rzhou-msft]

Hi @gittelena 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @gittelena 

I think you build two tables as below, build relationships(Many to one & Single) between Unique ID columns between two tables and build a slicer by On preferred list? column.

Customer Table:

Preferred customers Table:

Relationship:

Due to On preferred column don't have "NO"Values and two tables are related, so the slicer will show blank as "No".

You can achieve your goal by calculated column or measure. And you don't build relationships or add an On preferred column in Preferred customer Table.

Measure:

Build a Yes/No slicer.

M_Customer = 
VAR _Select =
    SELECTEDVALUE ( Slicer[On Preferred List?] )
VAR _PreferTable =
    VALUES ( 'Preferred customers'[Customer] )
VAR _Yes =
    CALCULATE (
        MAX ( Customer[Customer] ),
        FILTER ( Customer, Customer[Customer] IN _PreferTable )
    )
VAR _No =
    CALCULATE (
        MAX ( Customer[Customer] ),
        FILTER ( Customer, NOT ( Customer[Customer] IN _PreferTable ) )
    )
RETURN
    IF (
        ISFILTERED ( Slicer[On Preferred List?] ),
        IF ( _Select = "Yes", _Yes, _No ),
        MAX(Customer[Customer])
    )

Result is as below.

Default:

Select Yes/No in Slicer.

Or you don't need to do anything except building a calculated column in Customer table.

On Preferred list? = IF(Customer[Customer]in VALUES('Preferred customers'[Customer]),"Yes","No")

Result is the same as above.

You can download the pbix file from this link: Slicer based on relationships - how to change name of the option called (blank)

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@gittelena ,Can you share sample data and sample output in table format?

is this a calculated column then you should change the calculation to return No too.

new column

if(isblank([is no preferred list]),"No","Yes")

[AlexisOlson]

As far as I know, you can't change the name of (Blank).

The key here is that you need to categorize all of the customers whether they are on the preferred list or not. One way to do this would be to create a calculated/custom column on your master customer table that returns "Yes" for anyone on the preferred table and "No" otherwise. There are a variety of ways to do this but I'd recommend doing it in the query editor so you don't need to load both tables to your model.