removing empty Chiclets when I have no data to show in them

[Anonymous]

Hi community,

I need to have chiclets for a column that shows my data in a column but some cells don't have any data in them so the relevant chiclets look empty. How I can remove those blank chiclets instead of showing them?I try to use the following formula to remove them but still it triggers blank Chiclets up in my filters!

FM = if(TYPE[MNo.] <> "", TYPE[MNo.], "")

Please note that all the values here are type text and not numbers 

[v-eachen-msft]

Hi @Anonymous ,

You could use BLANK() to replace "".

Then set rule as "is not blank" in filter pane.

[Anonymous]

Hi @v-eachen-msft ,

Sorry we don't have is not blank as a function. My problem is to remove the chiclets that are EMPTY from my chiclets slicer!

@MFelix  Could you please have a look?

Thanks!

[v-eachen-msft]

Hi @Anonymous ,

You could create a new dimension table to do it. Here is the DAX for your reference.

Table 2 =
CALCULATETABLE (
    DISTINCT ( 'Table'[Your slicer column] ),
    FILTER ( 'Table', 'Table'[Your slicer column] <> BLANK () )
)

[Anonymous]

@v-eachen-msft The problem is exactly that blank cells are considered as repeated and so the KEY column in the table is not considered as a unique column to join! I don't want to remove blank cells because each blank cell is attached to another cells which has a vlaue and needs to be shown in the final table after importing to PBI from Power Query!

I don't find this solution any help to solve my original post.

But thank you and looking forward for other commetns

may be @MFelix  if likes to answer!

[MFelix]

Hi @Anonymous ,

If you are using a column from your table have you tried to simply filter out the values from the filter on the visual that are blanks?

Not really sure since when but some months ago the option to filter out blanks on slicers was added so this may solve your issue.

I'm assuming that when you refer to Chiclets it's the custom slicer available correct?

If this is not the case can you share a simple mockup data and expected result.