Change the x-axis dynamically

[Yogesh_NMH]

Hello everyone, 

I have a slicer for "Period Selection" (Yearly, Quarterly, Monthly, Weekly and Daily).

The x-axis on my charts need to change as per the selection made from the slicer as follows:

Yearly -> display Month/Year e.g. Oct 2016, Nov 2016, etc. up to the current month Jun 2016

Quarterly -> display Quarter/Year e.g. Q1 2017, Q2 2017, etc.

Monthly -> same as for Yearly

Weekly -> display Month/Year/Week e.g. May 2017 W1, May 2017 W2, etc.

Daily -> display all dates in the current month e.g. 2017-06-01, 2017-06-02, etc.

I have all those dates in my Date Dimension.

How do I create the Axis Dimension to reflect the above slicer selection please?

Thanks in advance.

Kind regards,

Yogesh

[Anonymous]

are you able to make it up? I am also looking for dynamic X-axis change based on the time range selection.

[theov]

Hi, yes basically you need to have an axis table combining your axes and then make inactive relationships with your model and call them in your measures. This video explains this subject thoroughly :https://www.youtube.com/watch?v=DXo7G6PtiYE

[vanessafvg]

@Yogesh_NMH why dont you create a hierarchy?

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-3-11g-visual-hierarchies-drilli...

[Yogesh_NMH]

@vanessafvg For some charts, I will, don't worry. The problem is, I need to reflect this Axis Dimension in several charts & KPI's as well. 

I am also using the custom visual found here. So when the user selects Yearly, Quarterly, Monthly, etc, the Trend Axis in the KPI Indicator needs to also change accordingly.

Any idea on how to do this?

Thanks in advance.

Kind regards,

Yogesh

[vanessafvg]

@Yogesh_NMH please post screen shots it really hard to visualise what you saying without providing examples and then also of what you actually wanting it to look like

[Yogesh_NMH]

@vanessafvg

The column chart below uses a Date Hierarchy which enables the user to drill down Year->Quarter->Month->Week->Day. 

However, for the KPI's below, you need to specify a Trend Axis. 

By default I have used the column MonthInCalendar. However, this axis needs to be dynamic. In simple english:

Trend Axis =

IF Yearly OR Monthly THEN MonthInCalendar

ELSE IF Quarterly THEN QuarterInCalendar 

ELSE IF Daily THEN DateInCalendar

ELSE MonthInCalendar (default)

[vanessafvg]

@Yogesh_NMH never personally done it before but think these 2 links can help

http://www.leanx.eu/tutorials/dynamic-dimensions-in-power-bi

https://gqbi.wordpress.com/2017/01/24/power-bi-how-to-filter-slicers-dynamically/

[Yogesh_NMH]

@vanessafvg I was looking at those 2 solutions some minutes ago 

[v-yulgu-msft · Accepted solution]

Hi @Yogesh_NMH,

Here is an other similar thread which has provided a valid solution to dynamically show X-axis dimension.

Dynamic change in X Axis

Best regards,
Yuliana Gu

[Yogesh_NMH]

Hi @v-yulgu-msft

Many thanks for your reply. I will give it a try.

Kind regards,

Yogesh