Dynamic Measure that modifies status based on filters

[hugo_barbara]

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 generate dynamic statuses for epics following some rules:

• The points worked on epics come from the issues linked to each epic.

• Issues go through multiple sprints, and this should influence the epic status as well — if an issue spans multiple sprints, the epic should reflect that information.

Ideally, I want to create a scenario like this:

ISSUE_KEY_EPIC CREATED DUE_DATE POINTS AWARDED PLANNED POINTS % COMPLETENESS BY SPRINT SPRINT_NAME

ABC-12123	27/05/2025	24/10/2025	5		100%	SP 25.3.1
ABC-12123	27/05/2025	24/10/2025	8	10	100%	SP 25.3.2
ABC-12123	27/05/2025	24/10/2025	2	2	100%	SP 25.3.3
ABC-12123	27/05/2025	24/10/2025	11	19	58%	SP 25.3.4
ABC-12123	27/05/2025	24/10/2025	5	13	38%	SP 25.3.5
ABC-12123	27/05/2025	24/10/2025		8		SP 25.4.2

The measure I created follows this logic:

SWITCH(
TRUE(),
ISBLANK([POINTS AWARDED]) || [POINTS AWARDED] = 0, "Not Started",
[POINTS AWARDED] < [PLANNED POINTS] && [DUE_DATE] > TODAY(), "At Risk",
[POINTS AWARDED] < [PLANNED POINTS] && [DUE_DATE] < TODAY(), "Overdue",
[POINTS AWARDED] >= [PLANNED POINTS] && [DUE_DATE] > TODAY() && [COMPLETENESS] < 1, "Rescheduled",
[POINTS AWARDED] >= [PLANNED POINTS] && [DUE_DATE] < TODAY() && [COMPLETENESS] < 1, "On Time",
[COMPLETENESS] >= 1, "Completed",
"Unclassified"
)

The problem is: when I use this measure in a table visual, it seems to go through the entire epics dataset, not respecting the context.

What could be the reason why this measure is not respecting the context of the visual?

[v-echaithra]

Hi @hugo_barbara ,

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.

[v-echaithra]

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]

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]

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.

[Kedar_Pande]

Add context to your completeness calculation:

Completeness by Sprint =
DIVIDE(
SUM(Issues[Points Awarded]),
SUM(Issues[Planned Points]),
0
)

The problem isn't your SWITCH logic , it's that one of your component measures is ignoring the sprint context.

[hugo_barbara]

hI @GeraldGEmerick 

Follow the modeling of the table

[GeraldGEmerick]

@hugo_barbara What does your data model look like and what columns from what tables are in your Table visual? This kind of sounds like a missing relationship scenario but it is difficult to know exactly.