Forum Discussion

gilmore_staci's avatar
gilmore_staci
Icon for Helper II rankHelper II
9 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 is based on the Incident Resolved date field

 

Is there a way I can get these in one clusted column chart to look like this?

 

DAX
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 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.

8 Replies

  • wardy912's avatar
    wardy912
    Icon for Super User rankSuper User
    9 months ago

    Hi gilmore_staci 

     

     You will need to have a date table with 1 active relationship to the sla table and 2 inactive relationships.

    Date open - Date

    Restored Date - Date (Inactive)

    Resolve Date - Date (Inactive)

     

    Now you will need 3 measures, the first measure will calculate the total 'met' slas, and can be used for response as your active relationship is 'date open - date', the others will use 'USERELATIONSHIP' to activate inactive relationships to provide restore and resolve figures:

     

    Total Met = sum(met)
    Restore Met = CALCULATE([Total Met], USERELATIONSHIP(restored date, date))
    Resolve Met = CALCULATE([Total Met], USERELATIONSHIP(resolved date, date))

     

    Add all 3 measures to your x axis

    --------------------------------

    I hope this helps, please give kudos and mark as solved if it does!

     

    Connect with me on LinkedIn.

    Subscribe to my YouTube channel for Fabric/Power Platform related content!

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    9 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.

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Icon for Super User rankSuper User
    9 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.
    • Ahmed-Elfeel's avatar
      Ahmed-Elfeel
      Icon for Super User rankSuper User
      9 months ago

      Hi gilmore_staci,

      AH! The issue is you are missing the Month context! You need to add the Month field to your visual

      So try to create a Month Column in Your Calendar Table:

      MonthName = FORMAT('Calendar'[Date], "mmm")

       

      here is some verify steps you should check:

      • Relationship to your main table via Date Open

      • Proper month sorting (use MonthNumber for sorting)
      • In Your Visual:
        • Are you using Calendar[MonthName] on X-axis?
        • Is Metric Table[Metric Name] in Legend?
        • Is SLA Performance in Values?

      90% of this issue related to visual fields so check it 

      • gilmore_staci's avatar
        gilmore_staci
        Icon for Helper II rankHelper II
        9 months ago

        ok I missed the Metric Table, but still seems to be a disconnect on the date.  I have this relationship, Date Opened is active, related to Date in calendar, Service Response and Incident Resolved is related to Date, not active. 

        When I pull the Date in to x-axis its just saying blank

         

  • gilmore_staci's avatar
    gilmore_staci
    Icon for Helper II rankHelper II
    6 months ago

    Ahmed-Elfeel the columns seem to not be in the correct order anymore.  I swear when you first helped me they were, now the legend is in Alpha order, even when I remove the sort order on the graph.  Any suggestions?

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    6 months ago

    Hi gilmore_staci,

     

    The reason your clustered column chart legend appears in alphabetical order is that Power BI sorts text fields alphabetically by default. Removing sorting from the visual won't change this; the legend will still be sorted A–Z unless you set a custom sort order in your data model.

    Since you've added an Order column to your disconnected Metric Table (with Response = 1, Restore = 2, Resolve = 3), you just need to apply it. In Data view, select the Metric Name column, click Sort by Column in the ribbon, and choose the Order column. This will ensure Power BI uses your custom numeric order instead of sorting alphabetically.

    Once this is done, your legend and clustered columns should display as Response, Restore, Resolve. If the order doesn't update right away, try refreshing the visual or removing and re-adding the Metric Name field to the legend.

     

    Thank you.