Forum Discussion

hugo_barbara's avatar
hugo_barbara
Helper I
10 months ago
Solved

Dynamic Measure that modifies status based on filters

Hello, I need to create a dynamic measure that updates the status of an item based on what is selected in a slicer. I’m working with a dataset of Issues and Epics from an agile model, and I want to...
  • v-echaithra's avatar
    v-echaithra
    10 months ago

    Hi hugo_barbara ,

    Please try this measure by using CALCULATE to enforce that the status measure respects the Sprint context. Below is an updated version of your measure that should handle the context appropriately:

    Epic Status by Sprint =
    VAR PointsAwarded =
    CALCULATE(
    SUM(d_EpicosIssues[STORYS_POINTS_REALIZADOS]), d_IssueStatus[ISSUE_STATUS_NAME] IN {"Accepted", "In Production", "Pending Production"})

    VAR PlannedPoints =
    CALCULATE(
    SUM(d_EpicosIssues[STORYS_POINTS_REALIZADOS]), d_IssueStatus[ISSUE_STATUS_NAME] <> "Canceled")

    VAR Completeness = DIVIDE(PointsAwarded, PlannedPoints, 0)

    RETURN
    SWITCH(TRUE(), ISBLANK(PointsAwarded) || PointsAwarded = 0, "Not Started",
    PointsAwarded < PlannedPoints && TODAY() < MAX(d_calendarioPISprints[SPRINT_END_DATE]), "At Risk",
    PointsAwarded < PlannedPoints && TODAY() >= MAX(d_calendarioPISprints[SPRINT_END_DATE]), "Overdue",
    PointsAwarded >= PlannedPoints && TODAY() < MAX(d_calendarioPISprints[SPRINT_END_DATE]) && Completeness < 1, "Rescheduled",
    PointsAwarded >= PlannedPoints && TODAY() >= MAX(d_calendarioPISprints[SPRINT_END_DATE]) && Completeness < 1, "On Time",
    Completeness >= 1, "Completed",
    "Unclassified")

    Hope this helps.
    Thank you.



v-echaithra's avatar
v-echaithra
Community Support
10 months ago

Hi hugo_barbara ,

Thank you hugo_barbara  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

hugo_barbara's avatar
hugo_barbara
Helper I
10 months ago

Hi v-echaithra i still need help..
I'll structure my problem better.


I'm struggling to build a DAX measure that determines the status of a Jira Epic per Sprint, based on the progress of its related Issues across multiple sprints.

The logic I want to apply is:
SWITCH(
TRUE(),
ISBLANK(PontosEntregues) || PontosEntregues = 0, "Not Started",
PontosEntregues < PontosPlanejados && DataLimite > TODAY(), "At Risk",
PontosEntregues < PontosPlanejados && DataLimite < TODAY(), "Delayed",
PontosEntregues >= PontosPlanejados && DataLimite > TODAY() && Completude < 1, "Rescheduled",
PontosEntregues >= PontosPlanejados && DataLimite < TODAY() && Completude < 1, "On Track",
Completude >= 1, "Completed",
"Unclassified"
)


Data Model Structure:
Key Tables:
f_Epicos: contains Epics with fields like ISSUE_KEY_EPIC, DUE_DATE, Created

d_EpicosIssues: links Epics to Issues (ISSUE_KEY, ISSUE_STATUS_ID, STORYS_POINTS_REALIZADOS)

d_IssuesSprintsRelacao: links Issues to Sprints (ISSUE_KEY_EPIC, SPRINT_NAME, SPRINT_FIM)

d_calendarioPISprints: Sprint calendar (NAME, SPRINT_ID)

Measures used:
TOTAL PLANNED POINTS =
CALCULATE(
SUM(d_EpicosIssues[STORYS_POINTS_REALIZADOS]),
d_IssueStatus[ISSUE_STATUS_NAME] <> "Canceled"
)

TOTAL DELIVERED POINTS =
CALCULATE(
SUM(d_EpicosIssues[STORYS_POINTS_REALIZADOS]),
d_IssueStatus[ISSUE_STATUS_NAME] IN {"Accepted", "In Production", "Pending Production"},
d_IssuesSprintsRelacao[SPRINT_FIM] = "Yes"
)


What I've tried:
I created a measure that attempts to filter Issues by the selected Sprint using TREATAS and CONTAINS, but even with a slicer or Sprint context in the visual, all Epics are still shown, ignoring the Sprint filter.

I also tried a simplified version assuming the visual already filters by Epic and Sprint, using fields like:

f_Epicos[ISSUE_KEY_EPIC]

d_Medidas[% Completion per Sprint]

[Planned Points]

[Delivered Points]

f_Epicos[Created]

f_Epicos[DUE_DATE]

Still, the status doesn't reflect the Sprint context correctly.

What I need:

I’m looking for a way to calculate the Epic status per Sprint, based on the Issues that passed through that Sprint, and reflect that dynamically in a table or matrix visual. The goal is for the Epic’s status to change depending on the progress of its Issues in each Sprint.

Any help with modeling, DAX logic, or best practices would be greatly appreciated!

d_IssueStatus: Issue status reference (ISSUE_STATUS_ID, ISSUE_STATUS_NAME)

Relationships:
f_Epicos[PROJECT_KEY] → d_Projects[PROJECT_KEY]

f_Epicos[ISSUE_KEY_EPIC] → d_EpicosIssues[ISSUE_KEY]

d_EpicosIssues[ISSUE_KEY] → d_IssuesSprintsRelacao[ISSUE_KEY_EPIC]

d_IssuesSprintsRelacao[SPRINT_NAME] → d_calendarioPISprints[NAME]

d_EpicosIssues[ISSUE_STATUS_ID] → d_IssueStatus[ISSUE_STATUS_ID]

 

 

  • v-echaithra's avatar
    v-echaithra
    Community Support
    10 months ago

    Hi hugo_barbara ,

    Please try this measure by using CALCULATE to enforce that the status measure respects the Sprint context. Below is an updated version of your measure that should handle the context appropriately:

    Epic Status by Sprint =
    VAR PointsAwarded =
    CALCULATE(
    SUM(d_EpicosIssues[STORYS_POINTS_REALIZADOS]), d_IssueStatus[ISSUE_STATUS_NAME] IN {"Accepted", "In Production", "Pending Production"})

    VAR PlannedPoints =
    CALCULATE(
    SUM(d_EpicosIssues[STORYS_POINTS_REALIZADOS]), d_IssueStatus[ISSUE_STATUS_NAME] <> "Canceled")

    VAR Completeness = DIVIDE(PointsAwarded, PlannedPoints, 0)

    RETURN
    SWITCH(TRUE(), ISBLANK(PointsAwarded) || PointsAwarded = 0, "Not Started",
    PointsAwarded < PlannedPoints && TODAY() < MAX(d_calendarioPISprints[SPRINT_END_DATE]), "At Risk",
    PointsAwarded < PlannedPoints && TODAY() >= MAX(d_calendarioPISprints[SPRINT_END_DATE]), "Overdue",
    PointsAwarded >= PlannedPoints && TODAY() < MAX(d_calendarioPISprints[SPRINT_END_DATE]) && Completeness < 1, "Rescheduled",
    PointsAwarded >= PlannedPoints && TODAY() >= MAX(d_calendarioPISprints[SPRINT_END_DATE]) && Completeness < 1, "On Time",
    Completeness >= 1, "Completed",
    "Unclassified")

    Hope this helps.
    Thank you.