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 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.

 

 

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

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

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

       

      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.

       

  • 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