Forum Discussion

Jeanxyz's avatar
Jeanxyz
Icon for Power Participant rankPower Participant
3 years ago
Solved

values() filter doesn't pass to the table

I have been fighting with a Dax formula for hours. I wonder if someone can help. 

 

I have an IssueSprint table as below. Each Jira issue is assigned to a sprint (10 work days). Each Sprint has a Start_Date and End_Date, if an issue is not solved during the period, it's passed onto next sprint. Some issues receive story points, which is an workload indicator. 

 

I have 58 story points in Demo Sprint 17, and I expect in each work day 10% issues are solved, so the remaining story points by end of day 1 is 90%*58, by day2, 80%*58 ....

 

I try to create a measure (guideline)  to duplicate this calculation logic, but it doesn't work. sample file: https://www.dropbox.com/s/0rxpfy4gfnxcucm/Jira%20Demo.pbix?dl=0

 

guideline(M) =
Var select_date=min(max('Calendar'[Date]),today())
var amt=
sumx(
values(IssueSprints),
calculate(max(IssueSprints[story_points])*
(1- Divide(
if(select_date<calculate(max(IssueSprints[Start_Date])),1,
//if select date<start date, completion rate=1,
if (select_date>calculate(max(IssueSprints[End_Date])),1,
//if select date>end date, completion rate=1,
calculate(sum(Calendar[Workday_BE]),Calendar[Date]<=select_date,Calendar[Date]>=calculate(max(IssueSprints[Start_Date])))
//select date falls betweeen start_date & end_date, calculate no of workdays from start_date to select_date
)),
10))))
return
amt
 
amitchandak , parry2k , @otravers, Greg_Deckler 
Need Help
  • Jeanxyz's avatar
    Jeanxyz
    3 years ago

    I think I figured it out. 

    There was a bug in the divide () part, also I created a dim_issuesprints table for use in the values(). 

     

    guideline =
    Var select_date=min(max('Calendar'[Date]),today())
    var amt=
    sumx(
    values(dim_IssueSprints),
    calculate(max(IssueSprints[story_points])* (1-
    calculate(
    divide(
    if(select_date<max(IssueSprints[Start_Date]),10,
    if (select_date>max(IssueSprints[End_Date]),10,
    calculate(sum(Calendar[Workday_BE]),Calendar[Date]<=select_date,Calendar[Date]>=max(IssueSprints[Start_Date])))),
    10)))))
    var start_date=minx(values(dim_issuesprints),calculate(max(IssueSprints[Start_Date])))
    return
    amt

2 Replies

  • Jeanxyz's avatar
    Jeanxyz
    Icon for Power Participant rankPower Participant
    3 years ago

    I think I figured it out. 

    There was a bug in the divide () part, also I created a dim_issuesprints table for use in the values(). 

     

    guideline =
    Var select_date=min(max('Calendar'[Date]),today())
    var amt=
    sumx(
    values(dim_IssueSprints),
    calculate(max(IssueSprints[story_points])* (1-
    calculate(
    divide(
    if(select_date<max(IssueSprints[Start_Date]),10,
    if (select_date>max(IssueSprints[End_Date]),10,
    calculate(sum(Calendar[Workday_BE]),Calendar[Date]<=select_date,Calendar[Date]>=max(IssueSprints[Start_Date])))),
    10)))))
    var start_date=minx(values(dim_issuesprints),calculate(max(IssueSprints[Start_Date])))
    return
    amt
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Jeanxyz,

    I'm glad to hear you find the reason and sharing the formula here, I think they may help other who faced the similar issue.

    Regards,

    Xiaoxin Sheng