Dynamically switch between Date and DateTime on X Axis of chart

[Anonymous]

Hello everyone! I have following scenario to implement:

- we have two tables with weather forecast. One is for next 15 days and have day granularity (15 records for a single city), second is for next 72 hours and have hour granularity (72 records for a single city).

- we would like to present forecast on one linear chart and allow end user to switch between daily and hourly forecast - the requirement is that X Axis will show dates or datetimes accordingly

First idea I had was to union these two tables, add attribute to distinguish daily and hourly records and use it in slicer but it would requires my Date dimension table to became DateTime dimension - I would rather avoid it as this hourly weather forecast data is the only one in the model that is not daily granulated. 

Then I tried to create Time dimension table but I was not able to present my 72 hours on X axis hour by hour. I guess it's good idea when we want to have hierarchy but in my case I just want to show 15/72 entries on X axis without any drill down.

I would be grateful for any help!

EDIT: maybe I will try to explain my need in an easier way:

Let's say we have fact table with date dimension table and time dimension table. If I use "Table" visualisation I can place Date column from Date dimension in the first column, then Time column from Time dimension in the second column and finally value from fact in third column. Values are presented using DateTime granularity. Is it possible to do the same in line chart? I want to have datetime on X Axis but want to keep Date and Time in separate dimensions. 

[Greg_Deckler · Accepted solution]

I would stack two visuals and use buttons and bookmarks. 2 buttons, 72 hours and 15 days. 2 bookmarks for 72 hours and 15 days and these show one visual and hide the other.

[v-deddai1-msft]

Hi @Anonymous,

As mentioned by Greg, you can combine the button and bookmark to fulfill your requirement. Please refer to the detailed steps in https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/

Best Regards,

Dedmon Dai

[Anonymous]

Thanks for reply @v-deddai1-msft

But what about case with separate Date and Time dimensions? Is it posible to present combined DateTime on X Axis of the chart? I failed to do that. As we want to have the smallest cardinality possible it's reasonable to have date and time in separate tables. This is what I want to have (with separate date and time dimensions):

Best regards!

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Your method may not be feasible. If you want to maintain a normal cardinality on the x-axis, you need to make the entire visual large (outside the report display range), which is not feasible. So I suggest you use the method I recommended above

Best Regards,

Dedmon Dai

[Greg_Deckler · Accepted solution]

I would stack two visuals and use buttons and bookmarks. 2 buttons, 72 hours and 15 days. 2 bookmarks for 72 hours and 15 days and these show one visual and hide the other.