Change the granularity of the x axis

[Bendelyon]

Hi everyones, 

I have a slicer of dates whitch allow me to choose the dates between i gonna visualize my chart graph.

Currently i show my data per months.

I want that my chart graph become dynamic and the conditions gonna be thoses ones:

- If the dates choosen are minus one month i want to see my data per weeks

- If the dates choosen are minus one week i whant to see my data per days

- Else i want to see my data per month

I thing that DAX measures can help me to find a solution but i do not know how i can made it.

If someone has a solution.

Thank you

[johnt75 · Accepted solution]

You can adapt the basic technique described in https://www.youtube.com/watch?v=KReYWx5NXYg but instead make it dynamic.

Create a field parameter containing the different granularities that you want, in your case days, weeks and months. Edit the code of the field parameter table to add 2 new columns, which will specify the min and max number of days which you want to show at that granularity. Use BLANK() for the largest granularity's max. Rename the new columns to [Min Days] and [Max Days]. You will have a table something like

You can obviously use whatever numbers you like for the min and max for each granularity, but make sure that the max of one granularity is the same as the min for the next one.

Create a measure like

Choose Date Precision = VAR FirstVisibleDate = MIN( 'Date'[Date] )
VAR LastVisibleDate = MAX( 'Date'[Date] )
VAR DaysDiff = DATEDIFF( FirstVisibleDate, LastVisibleDate, DAY)
VAR MinDays = SELECTEDVALUE( 'Date Precision'[Min Days] )
VAR MaxDays = SELECTEDVALUE( 'Date Precision'[Max Days] )
VAR Result = IF( MinDays <= DaysDiff && ( ISBLANK( MaxDays ) || MaxDays > DaysDiff ), 1, 0 )

RETURN Result

Add the field parameter to the visual, and then add the field parameter as a filter on the visual. Set the filter to be TopN, use 1 as the number of items to show, and use the measure as the value.

Now the visual will change granularity based on the number of visible dates

[lbendlin]

Throw away your hierarchy.  Add the Date column to the X axis by itself.  Set the axis type to Continuous. Enable Zoom Sliders.  The visual will do all the rest of the work for you.