How to Hide Useless Date Choices In Filter Box and X Axis

[Anonymous]

I use date table's Year & Month for the filter box to customer for report period selection, but the droplist shows all months of 2022, which only January has available data. I want to hide the choices from Feb.2022 to Dec.2022, please help me.

I also want to remove useless months on X axis from Feb.2022 to Dec.2022. I can't use filter to remove the column values equal to zero because the values are not continuous.

[parry2k]

@Anonymous I'm bit surprised why they are showing in the first place, what is the measure that you are using? Also, did you selected show all items with no data

[Anonymous]

This is a simple column chart linked one excel table, I use Excel column "cn_date" as time axis and the count of row with same day as measure. When I drilled down the column chart to show the sum count of each month, Power BI shows all months in the year on X axis, no matter it is blank or not.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can try formulas like the following:

inf = CALCULATE(Min('Table'[Date]),FILTER(ALL('Table'),'Table'[Value1]<>0||'Table'[value2]<>0))

sup = CALCULATE(Max('Table'[Date]),FILTER(ALL('Table'),'Table'[Value1]<>0||'Table'[value2]<>0))

filter = IF(SELECTEDVALUE('Table'[Date])>=[inf]&&SELECTEDVALUE('Table'[Date])<=[sup],1,0)

Then apply measure 'filter' to the filter pane of your chart.

Please check my attachment for more detail.

If it doesn't work, please share your sample file for further research. 

Best Regards,
Community Support Team _ Eason

[parry2k]

@Anonymous you can add your measure as visual level filter with "is not blank" and that will do it.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Not all blank months should be hided. For example as below, I want to hide both sides' blank months automaticly and keep the blank ones in the middle.