Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 choice...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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