Powerbi charts update the x axis value run time based on slicer selection.

[giridhar196]

I have graph which is build with UTC time zone and hours

Table 

DATE_UTC	HOUR_UTC	Sale
2022-10-01	13	100

The graph is using 

x- axis date and hour[date]

y - axis total sales 

I added a slicer called as time zone. it has off set and zone name.

when user selected a time zone like CST, The UTC dates should get updated with add of off set value of 6 hrs.

This dynamic values are rendaring with measure. as same like date I create a measure which updates hours based on zone.

Problem:

I am not able to use measure in x -axis for date and time.

If i use a column, the values in column will not update based on slicer selection in run time.

Can any one suggest what I can used in x axis which render data on run time.

I cannot create like this, because my data is huge.

1|2022-10-1|13|100|UTC

2|2022-10-1|19|100|CST

Thanks advance.

[TshepangMahlang]

This is not a problem to enter dates in the X axis of a line chart (or other chart type that uses a date type axis). This IS a problem for a value axis, that is, any Y axis or the X axis in an XY chart. This functionality was broken in Excel 2007, and despite complaints and bug reports, it remains broken in 2010.

[Anonymous · Accepted solution]

Hi @giridhar196 ,

Here I suggest you to try Field in "What if Parameter" function to create a dynamic x axis.

Your table should look like as below, the datazoom value should in your data model.

Result is as below.

CST:

UTC:

Best Regards,
Rico Zhou

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

[giridhar196]

@Anonymous 

Perfect solution, but I have a problem of defining the data type. Power bi date format has special features, I am not able to convert the parameter into date.

warning is - cannot convert cst of type text to type date while processing column- parameter 

[lbendlin]

Date.From(DateTime.From([CST]))

[giridhar196]

@lbendlin 

Can we use this in the Date.From insted of Nameof() in the parameters ? 

Parameter.DateData = {

    ("CST",  NAMEOF('table1'[CST]), 0),

    ("UTC", NAMEOF('table1'[Date]), 1)

}

[lbendlin]

No, these functions only work in Power Query.

Is your business requirement limited to CST and UTC or is the expectation that the users can specify arbitrary timezone shifts?

As you have noticed you cannot have measures as the X axis for some of the visuals. You would indeed have to create a text representation of all the potentially chosen timezones beforehand and use crossjoins.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.