cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Salzstangen
Frequent Visitor

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

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!

 

1 ACCEPTED SOLUTION

Hi @Salzstangen 

 

You can replace the day field with the date field.

Remove Day.

 
 

image.png

And add Date

image.png

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

 

View solution in original post

6 REPLIES 6
v-jayw-msft
Community Support
Community Support

Hi @Salzstangen ,

 

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:

5.PNG

Here's the calculated table:

6.PNG

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

7.PNG

8.PNG

 

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.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
MFelix
Super User III
Super User III

Hi @Salzstangen 

 

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

 


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



Mariusz
Super User II
Super User II

Hi @Salzstangen 

 

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

 

 

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

 

 

Hi @Salzstangen 

 

You can replace the day field with the date field.

Remove Day.

 
 

image.png

And add Date

image.png

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

 

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.