Dynamic y axis max value for line chart with date hierarchy in x axis

[karimm]

hi All

I have a line chart with hierarchy of 3 levels in x axis (Month->Week->Day). It shows numerical values in x axis which cannot be limited to a specific range.
The user can drill up/down.

I need the chart to start at 0, so I've set Y axis min to 0 and max to Auto

But when filtering to a single date, I get the following single half circle data point: 

As you can see, the data point is cut from the top.

Is there:

A. an easy fix for this? OR

B. do I need to set the y axis max dynamically by calculating the maximum on all x-axis and adding 10%?

If the answer is B, I'd appreciate the help in writing the DAX as I'm not able to get it to work...

This is the hierarchy in x-axis:
CalendarAuto[Month]
CalendarAuto[Week]
CalendarAuto[Day]

And the measure is called [coding_time]

I tried this:

VAR MaxVal = SWITCH( TRUE(), ISFILTERED(CalendarAuto[Day]), MAXX ( SUMMARIZE ( ALLSELECTED (CalendarAuto[Day]), "Value", [coding_time] ),[Value] ), ISFILTERED(CalendarAuto[WeekStart]), MAXX ( SUMMARIZE ( ALLSELECTED (CalendarAuto[WeekStart]), "Value", [coding_time] ),[Value] ), ISFILTERED(CalendarAuto[Month-Year]), MAXX ( SUMMARIZE ( ALLSELECTED (CalendarAuto[Month-Year]), "Value", [coding_time] ),[Value] ) ) RETURN MaxVal * 1.1

but it's not giving the same max value on each day/each month/each week:

So it's clear that the context being read is incorrect.

Help 🙂

[karimm · Accepted solution]

So turns out it's simpler than i thought. 

The correct measure to put in the y-axis max should look like this

MAXX(ALLSELECTED(CalendarAuto), [coding_time) * 1.1

[OwenAuger]

Interesting 🙂 I would have thought that would give you a maximum at a Date level (being the granularity of the CalendarAuto table) which is what it does in my test model, but there must be some reason it works in your case.

On to the question of using ISINSCOPE or ISFILTERED checks in a measure to determine the Y-axis maximum: The measure provided for the Y-axis maximum is evaluated at the "grand total" level, so ISINSCOPE or ISFILTERED will generally not return the same result as they would at an individual data point.

Suggestions on solving the display issue with white marker interrupting vertical line:

You could turn off the line (or change it to zero width), turn on marker, and make the marker transparent.

You could also use a visual calculation that returns the required value at a single point on the visual (e.g. the leftmost point) and hide it in a similar way.

Updated PBIX attached in case useful.

[OwenAuger]

Hi @karimm 

The tricky thing here is that the measure provided for Y-axis Maximum is evaluated at the overall visual level, and cannot detect the current X-axis hierarchy level.

This is a problem even with visual calculations, as they also can't detect X-axis hierarchy level when evaluated at overall visual level.

In this case I would suggest creating a measure that scales coding_time:

coding_time inflated =
[coding_time] * 1.1

Add this measure to the visual, set its line width to zero and colour to white, edit its field name to a space.

Tooltips may need customizing if that's an issue.

Small example attached.

Would this work for you?

[karimm]

Thank you very much for you reply.

Regariding not being able to detect the dimension, why can't functions like ISFILTERED, ISINSCOPE help here? I'd appreciate if you can explain further or refer to an article about it.

An regarding the suggested solution, It's a great workaround. Thank you! 🙂

I tried it now and since I already have custom tooltips, the additional line doesn't cause an issue.

The only downside I can see, is that when hovering over a data point, PowerBI displays a gray vertical line and now due to the white colored line, this vertical line has a small gap.

See below on the right side of the image.

Any suggestions about it?