Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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 

5 REPLIES 5
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

You could use BLANK() to replace "".

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

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!

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 () )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

@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!

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.