Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
dwightman2176
Frequent Visitor

Graph/Table showing more dates than needed on x-axis

Hello everyone. I'm hoping someone can help a bit of a Power BI novice here.

 

I'm trying to build a visual using a Line and Clustered Column chart. The chart should contain the last 6 full calendar months' downtime as one data point and availability % as the other data point, using the incident end date on the x-axis for the date range. The issue I am having is that when I plot the downtime on the chart, it works - just shows the last 6 calendar months' data. When I add the availability %, though, the date range extends to the beginning of 2022 and through to the end of 2024. I've tried using filters, but the availability % doesn't seem to adjust.

I can get around the situation temporarily this month by adding a filter which excludes entries where downtime = 0, but this would then not show entries on the visual where we genuinely haven't had any downtime.


For a little more information....

  • On the Column Y axis I'm using the SUM of a calculated column - Downtime
  • On the Line Y axis I am using the % Availability Measure
  • On the X axis I am using the incident end date column to plot the dates using a calendar hierachy
  • I assume it's the availability calculation that is causing this, as it is outputting 100% for the months where there is no actual data? This is how this is being calculated:
    • % Availability = 1-SUM(Issues[Impact on Uptime (%)]
      • Impact on Uptime (%) = DIVIDE(Issues[downtime ], Issues[Mins in month]
    • Downtime = DATEDIFF(Issues[Impact Start Date], Issues[Impact End Date], MINUTE

 

Visual with just downtime

dwightman2176_1-1708593945803.png

 

Visual with downtime and % availability

dwightman2176_0-1708593902116.png

Any help would be massively appreciated, and please let me know if I need to include some additional information.

 

Thanks
David

1 ACCEPTED SOLUTION

Thanks again . I played around with your suggestion and eventually came up with the following:

6-Month Availability % =
    var _MinDate = TODAY()-184
    var _MaxDate = TODAY()
    RETURN
        IF(
            ENDOFMONTH(
                Issues[End Date.[Date]) <= _MinDate,
                BLANK(),
                IF(ENDOFMONTH(Issues[End Date].[Date]) > _MaxDate,
                BLANK(),
        'Issue Measures'[% Availability]
        ))
dwightman2176_0-1708686154361.png

Its not the perfect solution as it only works on a report looking at the past 6 months, but it will do for now until we can come up with something more flexible.

 
Thanks again for your help,
David

View solution in original post

7 REPLIES 7
dwightman2176
Frequent Visitor

Thanks again @_AAndrade . That fixes the December issue, but re-introduces the other dates into the visual

 

dwightman2176_0-1708613591900.png

Thnaks

David

Probably to do what you want, you need to see the min and max date of the x-axis that has value and after apply the measure.
For example:
VAR _MinDate = CALCULATE(MIN(Date), [measure] <>0)
VAR _MaxDate = CALCULATE(MIN(Date), [measure] <>0)
RETURN
IF(
   Selectvalue(Date) >= _MinDate && Selectvalue(Date)<= MaxDate,
   [measure],
  Blank()
)

Probably the measure that I wrote need to fix some issues but the idea is this.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Thanks again . I played around with your suggestion and eventually came up with the following:

6-Month Availability % =
    var _MinDate = TODAY()-184
    var _MaxDate = TODAY()
    RETURN
        IF(
            ENDOFMONTH(
                Issues[End Date.[Date]) <= _MinDate,
                BLANK(),
                IF(ENDOFMONTH(Issues[End Date].[Date]) > _MaxDate,
                BLANK(),
        'Issue Measures'[% Availability]
        ))
dwightman2176_0-1708686154361.png

Its not the perfect solution as it only works on a report looking at the past 6 months, but it will do for now until we can come up with something more flexible.

 
Thanks again for your help,
David

You just need to dynamically configure the _Min and _Max variables according to your model. That's why I used calculate.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




_AAndrade
Super User
Super User

I think the problem is on "% Availability" measure. Put some IF function that only do the "% Availability" if Downtime has value.

Something like this:
IF(NOT(ISBLANK( [Downtime])), 1-SUM(Issues[Impact on Uptime (%)], BLANK())





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Thanks @_AAndrade . That has almost solved it. Definitely a step forward. The issue I am left with now is where there is no downtime in a given month, that month is not presented in the visual, whereas I would like it to just show as being blank. As you can see in the attached - December is not presented on the visual

 

dwightman2176_0-1708605480873.png

Thanks,

David

Replace BLANK() to 0 (zero). I think it could solve the problem





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.