Change X Axis Start and End Depending Upon Data In Graph

[Everton]

Hi,

I have a graph which displays appointments booked by time:

The X Axis is at 15 minute intervals. This is my data model, the table v_15MinuteIntervals is a dimension table with a row for every 15 minutes in a day:I have my graph axis defined like this:

What I want to do is have my X axis start and end at the time when appointments start and end, so rather than going for a 24 hour period it would start around 6 AM and end around 2:15 PM. In the X-Axis properties I can see we can define Start and End properties. I can't seem to set these as a value from my data set though. I can only select a date, but I need these to be dynamic. 

Is there anyway to set the Start and End properties on the X-Axis to be a value from my data set?

[Everton]

Update:

I have created two measures, one with the first time of the appointments, one with the last time of the appointments. Is there a way I can make the X-Axis stay within these parameters?

Appointments Start At = Min(v_Appointments[Appointment Time])

Appointments End At = MAX(v_Appointments[Appointment Time])

[OwenAuger]

@Everton 

Normally the x-axis will adjust automatically to exclude ranges

1. Below the minimum value where any measure is nonblank
2. Above the maximum value where any measure is nonblank.

It looks like your Appointment Count measure is returning zero instead of BLANK over certain ranges. Ideally, you should define Appointment Count so that it returns BLANK rather than zero. Is this possible in your situation?

Normally this happens automatically with measures using COUNTROWS or DISTINCTCOUNT, but your measure may be more complex. You can always write the measure to replace zero with BLANK, or you could incorporate the bounds you described above into the measure definition.

Could you post the definition of Appointment Count?

Regards,

Owen

[Everton]

Hi Owen,

Yes, you are right, I have made it return 0 rather than blank:

Appointment Count = Count(v_Appointments[Appointment]) + 0

I needed the graph to dip down to 0 rather than blank when there is no appointments. I guess the ideal situation is if I can return blank if the time is before or after the first and last appointment times.

[OwenAuger · Accepted solution]

Ahh I see,

There are many ways you could do this. Here is one that should work.

Note ALLSELECTED could be changed to ALL to give absolute min/max times with data:

Appointment Count =
VAR MinTimeWithData = 
    CALCULATE ( MIN ( v_Apointments[Appointment Time] ), ALLSELECTED () )
VAR MaxTimeWithData =
    CALCULATE ( MAX ( v_Apointments[Appointment Time] ), ALLSELECTED () )
VAR MinTimeFiltered =
    MIN ( v_15MinuteIntervals[15MinInterval] )
VAR MaxTimeFiltered =
    MAX ( v_15MinuteIntervals[15MinInterval] )
RETURN
    COUNT ( v_Appointments[Appointment] )
    + IF (
        MinTimeFiltered <= MaxTimeWithData && MaxTimeFiltered >= MinTimeWithData,
        0
    )

Regards,

Owen