Help with blank values in slicer

[Anonymous]

 Hey all,

I have a dataset from my 'Areas' table like so:

I'm trying to use the Slicer visual (the out the box one in Power BI) but with a hierarchy for the different area levels.

However, when I add the values, I get a series of (Blank) values in the slicer as well, like so:

When I try to filter out (Blank) it in turn removes any area levels that have a blank, so my slicer cannot be used:

Any tips on how I can fix this?

[v-rzhou-msft]

Hi @Anonymous 

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

[Anonymous]

I wouldn't say my problem has been solved - a workaround has been presented but not the solution I'm looking for 😞

[v-rzhou-msft]

Hi @Anonymous 

I build two tables related by AreaSK columns (Many to one )like yours to have a test.

Area Table:

Workitem Table:

Relationship:

Build a hierarchy slicer by different arealevels.

Due to your type of your arealevel is Text, is blank or is not blank is not useful.

You can change the Filter Type in Arealevel3 to Basic filtering, then click select all and click blank again, so you can filter out the blank value in your slicer.

Default:

Filter out blank value.

Or you can filter out blank value in Power Query Editor directly.

The result is the same as above.

You can download the pbix file from this link: Help with blank values in slicer

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. 

[Anonymous]

Thanks but that is not like the scenario I am dealing with 😕 

If you see in my image there are some AreaLevel2 values that do not have an AreaLevel3 (see BRS OU or Conflict Management)...

[v-rzhou-msft]

Hi @Anonymous 

I changed my data model.

I think you want to hide the blank value without impact the hierachy structure. Ex: Remove blank and still show C in arealevel2.

You may try Hierachy Slicer in custom visual.

Select Empty in Hide Members and trun on the string are empty.

Result:

If this reply still couldn't help you solve your problem, please share your pbix file by your Onedirve for Business.

And you can provide me with the screenshot of the result you want.

You can download my pbix file from this link: Help with blank values in slicer

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. 

[Pragati11]

Hi @Anonymous ,

Are there any relationships on this table with some other table?

Thanks,

Pragati

[amitchandak]

@Anonymous , there is an option there is not blank. Use that do not use contains

[Anonymous]

@amitchandak Ah good spot. I tried that though and it did not work 😞

@Pragati11 it has a relationship with a table of items 'WorkItems Completed' (the slicer filters them), they have the following relationship:

[Pragati11]

Hi @Anonymous ,

Can you try one thing at your end please:

Create a manual hierarchy in your AREAS table which has got all the 3 columns used in slicer.

Under Fields Pane, Ricght-Click on your Area1 column and select New Hierarchy as shown below:

Once you do this a new hierarchy will be created in your table.

Add Area2 and Area 3 column to this hierarchy (by right-clicking on them and using option ADD TO HIERARCHY) in the order you want them to be in the slicer.

Area1 -> Area2 -> Area3

Now try to move this hierarchy to your slicer and check if you still have blanks.

Let me know how you get with this.

Thanks,

Pragati

[Anonymous]

Hey @Pragati11 thanks for the suggestion, sadly this didn't completely work. I still get blanks and when filtering out blanks it then has the same adverse effect 😞

[Pragati11]

Hi @Anonymous ,

I just tested similar scenario at my end, looks like this will not work.

Let me give a thought on this. It looks tricky as BLANKS are in your data.

Can't you replace BLANKS with something like UNKNOWN in data?

Thanks,

Pragati

[Anonymous]

Unfortunately not - that would confuse the end user 😞