Forum Discussion
Handling fall time change in continuous Line Chart (also, how to format the X axis?)
Hi mjc123 ,
There are potentially two workarounds in your scenario, You can address this by creating a new calculated column that explicitly handles the duplicate hour scenario instead of letting Power BI aggregate it implicitly. The idea is to build a column that detects when the same Ept timestamp appears more than once (like the repeated 01:00 AM during DST change) and slightly differentiates or tags those rows so they are treated as unique points on the axis. For example, you can use DAX to count occurrences of each timestamp and append a sequence or offset to the duplicate instances, which is potentially being used by you currently if I am not wrong. Another workaround is, you can handle this at the measure level by creating a DAX measure that intentionally ignores one of the duplicate timestamps so the visual doesn’t aggregate both values. The idea is to rank rows for each repeated Ept value (using a stable column like Utc), and then return the value only for the first occurrence while blanking out the rest. This way, the visual plots a single point for that hour without summing duplicates in Power BI.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi grazitti_sapna,
I'm not sure if your version of Power BI is working a little bit differently than mine, but the chart in your latest file looks very similar to what I had taken a screenshot of in my previous post. i.e:
It's the X-Axis labels that are the problem:
My ideal solution would be something like this (with or without the TZ indicator):
I realize I can do it by setting the axis to "Categorical", and that would work for small timescales like the above - but it breaks when trying to display, for example, an entire month (or more) of hourly data -- unless there's a setting somewhere to hide some of the category labels.
I can see you added an "EPT Display" column in your data, and I follow the math but for some reason it isn't evaluating properly for me:
It does work if I add a helper column... not sure why it isn't evaluating properly without it:
But either way, thsoe "EPT Display" values don't end up showing up in the X-Axis labels, only in the tooltips:
as noted previously, I can get exactly what I want by changing it to categorical:
but again, that's only good with a small data set. if I use the full month's data, I get this:
labels are correct, but now there's a scroll bar and that's not what I'm looking for.
I think that what I'm looking for simply isn't possible with the standard line chart visualization. I'll have to do a work-around like what I noted in my previous post, by adding 30 minutes to the duplicate hour or something like that.
Hi mjc123 ,
There are potentially two workarounds in your scenario, You can address this by creating a new calculated column that explicitly handles the duplicate hour scenario instead of letting Power BI aggregate it implicitly. The idea is to build a column that detects when the same Ept timestamp appears more than once (like the repeated 01:00 AM during DST change) and slightly differentiates or tags those rows so they are treated as unique points on the axis. For example, you can use DAX to count occurrences of each timestamp and append a sequence or offset to the duplicate instances, which is potentially being used by you currently if I am not wrong. Another workaround is, you can handle this at the measure level by creating a DAX measure that intentionally ignores one of the duplicate timestamps so the visual doesn’t aggregate both values. The idea is to rank rows for each repeated Ept value (using a stable column like Utc), and then return the value only for the first occurrence while blanking out the rest. This way, the visual plots a single point for that hour without summing duplicates in Power BI.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi Anonymous , thanks for that. I think that's where my head ended up as well. Neither of the two options is ideal - but both will accomplish what I'm looking for at least in terms of the visual. For my calculations (in a matrix table) I need to include both hours, but at least for the visual nobody will notice a single hour missing.
I'll mark your post as the solution because I think it's the best we'll get without some fundamental changes to the way the visuals are handled; perhaps in some far flung update down the road 😄Hi mjc123 ,
We really appreciate your efforts and for letting us know the update on the issue.
Please continue using fabric community forum for your further assistance.
Regards