Forum Discussion

gilmore_staci's avatar
gilmore_staci
Icon for Helper III rankHelper III
10 months ago
Solved

clustered column chart with 3 different data points that use 3 different date fields

I have 3 formula fields that calculate if the SLA was met for Response, Restore and Resolve Response Met is based on Date Open Restore Met is based on the Service Restored date field  Resolve Met ...
DAX
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    10 months ago

    Hi gilmore_staci,

    So you want to Create Like a Combined Summary Report right?

    •  First you need create a Metric Table: (Disconnected)
    Metric Table = 
DATATABLE(
    "Metric Name", STRING,
    "Order", INTEGER,
    {
        {"Response", 1},
        {"Restore", 2},
        {"Resolve", 3}
    }
)
    • Also create the Three Base Measures :
    Response SLA % = 
DIVIDE(
    CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Response Met] = 1),
    COUNTROWS('YourTable'),
    0
)

Restore SLA % = 
DIVIDE(
    CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Restore Met] = 1),
    COUNTROWS('YourTable'),
    0
)

Resolve SLA % = 
DIVIDE(
    CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Resolve Met] = 1),
    COUNTROWS('YourTable'),
    0
)
    •  Then Create the main DAX Measure:
    SLA Performance = 
VAR SelectedMetric = SELECTEDVALUE('Metric Table'[Metric Name])
RETURN
SWITCH(
    SelectedMetric,
    "Response", [Response SLA %],
    "Restore", [Restore SLA %],
    "Resolve", [Resolve SLA %],
    BLANK()
)

     

    Tell me if this works ☺️❤️

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
v-sgandrathi's avatar
v-sgandrathi
Icon for Community Support rankCommunity Support
10 months ago

Hi gilmore_staci,

Thank you Ahmed-Elfeel and wardy912 for your response.

The clustered column chart shows blank values when you add the Date field because it lacks the metric context needed by the SLA Performance measure. The measure relies on SELECTEDVALUE('Metric Table'[Metric Name]) to switch between Response, Restore, and Resolve, so the Metric Name column must be included in the visual. If only the Date field is on the X-axis, the measure can't identify which metric to calculate and returns blank. To fix this, keep your current relationships and set up the visual with Calendar[MonthName] on the X-axis, Metric Table[Metric Name] as the Legend, and SLA Performance as the Values field. This way, both month and metric context are provided, allowing the chart to display Response SLA %, Restore SLA %, and Resolve SLA % as three clustered bars per month instead of blanks.

 

Thank you.