Dynamic X-Axis with date and time issues

[bi_powered]

Hi,

I've Telemetry data being brought from Cosmos DB into Power BI. While doing this, there is a specific need to allow time-frame selection to users. The options being (each option shows average value of Measure):

1. Last 1 Hour: X-Axis shows each minute

2. Today: X-Axis shows each hour

3. Last Week: X-Axis shows each hour

4. Last Month: X-Axis shows each day

5. Last Year: X-Axis shows each Month

To achieve this, I tried to apply the approach mentioned in: https://community.powerbi.com/t5/Desktop/Dynamic-Date-Axis/m-p/582859 for Date and Time with no luck.

How can the approach mentioned in above link be applied for both Date and Time? Am seeing wrongly aggregated values everywhere and it's an aggregation of all date and time (instead of matching date and time).

Please help for this. Any help will be greatly appreciated and will be highly obliged for the same.

For reference, here are my screenshots:

[lbendlin]

The approach you mentioned will work but at a very high cost.  Your telemetry data likely has extremely high cardinality, and you would need a disconnected table with at least four rows for each of your telemetry data points (minute, hour, day, month).

Have you considered using zoom sliders instead?  They would immediately solve the problem and would add a lot of flexibility.

[bi_powered]

Hi @lbendlin - thanks for the comment, but my problem is that the approach I've taken is not working, and am not sure why. I do agree that the cardinality would be high, but we don't have more than a record per 15 minutes, so that should be generally fine at initial levels. 

Can you help to understand looking at images above on where there is a mistake in DAX Expression? As have shared screenshot, am seeing values for date and time that don't exist in Power BI Model. 

Thanks in advance.

[lbendlin]

Is your data source coming from an import or from a direct query connection?  If the former then you need to be aware that the refresh frequency will have a strong impact on the user experience - basically you would have to refresh every minute.

I would like to repeat my recommendation that instead of your approach you may want to consider using zoom sliders with your raw data - no calculated tables required).

One side note - typically telemetry data is fed into streaming datasets.  What made you decide against that option?