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

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.

gilmore_staci

Helper III

10 months ago

Ahmed-Elfeel I'm close with your solution. I only get 1 data set, I need them filtered by month

Ahmed-Elfeel

Super User

10 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
Helper III
10 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

Forum Discussion

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