Switch between 1 day and 1 month bin size for time axis from visual through slicer

[Anonymous]

Is there a way to create a slicer to change the bin size of the date axis of my visual between a bin of 1 month and 1 day?

Just like I would do the following:

-go to my visual

-right click on the date field in the axis section

-open the edit groups pane and change the bin from 1 month to 1 day / vica versa

-->But I would like to create a slicer to be able to select this option within the report.

Thanks in advance!

[v-jayw-msft]

Hi @Anonymous ,

Normally you can use Date Hierarchy in Power BI.

https://5minutebi.com/2017/11/29/how-to-use-powerbi-date-hierarchy/.

If you want a slicer to let you chang options, you might to create a calculated table.

Please check the measure below.

Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", FORMAT ( 'Table'[date], "YYYY-MM-DD" ),
        "value", 'Table'[value],
        "type", "day"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", FORMAT ( 'Table'[date], "YYYY-MM" ),
        "value", 'Table'[value],
        "type", "month"
    )
)

 Here's my raw data:

Here's the calculated table:

Then you can use the type column as a slicer to change the visual.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous 

Check this solution from RADACAD with an option to change bin sizes, they are using ages but maybe can be adjust to what you need.

https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-count-of-bins

[Mariusz]

Hi @Anonymous 

You can use drill up / down
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz,

good proposal. It works out fine for years and months but if I drill down to days, PBI groups all days together without showing me the month or year.

So if I'm using a time span of 5 years and drill down to days, the x axes only shows me the number for the days (1-31) without the years and months..

[Mariusz · Accepted solution]

Hi @Anonymous 

You can replace the day field with the date field.

Remove Day.

And add Date

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Sorry for my late reply and thanks Mariusz.

It is a solution, although not really a handy one, because as far as I know the drill down functionality can be set only for one visual, so there is no way to synchronize them.

Is there maybe any other way to achieve this with the help of a dropdown slicer? In my eyes this would be the most user friendly way..