(Blank) Value in Slicers

[Anonymous]

I read on a previous forum post that the (Blank) values (despite their being no blank values in the dataset) in slicers are due to missing values from one of the datasets after creating the relationship. I have since removed the missing values in the query editor and now the relationship column has the same values in both columns.

I am trying to use ISCROSSFILTERED to create a dynamic title, but it is ruined when I filter out the (Blank) values from the report as it picks up that a filter is active.

Does anyone know a way to fix this? 

[v-shex-msft]

Hi @Anonymous,

If your slicer appears blank value after create relationship, it mean your tables contains unmatched records.

You can refer to following link to know why it appears:

Slicer showing Blank as a value when data does not have any blank values

In my opinion, I'd like to suggest you create a calculate table with distinct value from both table. Then use it as bridge to link two tables and create slicer.

Formula:

Bridge =
DISTINCT ( UNION ( VALUES ( Table1[Column] ), VALUES ( Table2[Column] ) ) )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks @v-shex-msft for the swift reply:

I went through my datasets and found one value which was in the 'one' side of the relationship but not in the 'many' side. I then filtered out this value in the query editor so it never entered the dataset. However I'm still getting (blank) values when trying to enter slicers - I even created two slicers side by side with the same field from both sides of the relationship, and both slicers had identical values with (Blank) in both.

[v-shex-msft]

Hi @Anonymous,

You can try to use below measure to find out unmatched records:

Unmatched Records =
CONCATENATEX (
    DISTINCT (
        EXCEPT ( ALLSELECTED ( Table1[Column] ), ALLSELECTED ( Table2[Column] ) )
    ),
    [Column],
    ","
)

Notice: table 1 is 'many' side table, table 2 is 'single' side table.

BTW, please check your table if it contains blank records, it also cause the blank slicer item issue.

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft thanks for the advice, although it shows that there are no unmatched records between the two datasets?

[v-shex-msft]

Hi @Anonymous,

It sounds odd, can you please share a pbix file with some sample data for test?

Regards,

Xiaoxin Sheng