SUM Distinct for table with duplicate values

[chipchidster]

I am struggling with an issue that i am sure is easier to solve that I am making it!

I'm trying to create a graph showing velocity for each of our software teams.  This is being driven from a summary table that contains all issues that moved to a given status, grouped by iteration date.  The graph has a slicer that allows people to set an end status (some squads measure velocity at different points in their workflow).  All of that works fine - however, for some status' I can see that the issue moves into that status more than once in an iteration, which means I end up double (or triple) counting.  I cannot figure out how to only count the story points for each issue only once. 

The table is being generated from a history table that stores each transition with the following DAX:

This gives me the following in my table (as you can see - this issue has moved into QA 4 times.  The max_sprint_date allows us to group issue transitions into our iterations (in this case, the issue below has only existed within the same iteration)

If I then filter my graph to report velocity for issues going to QA, I will get the following:

 Which means i will be reporting a velocity of 4 rather than 1 for this issue.

I have tried playing around with the DAX to only bring 1 transition per issue key, status and max_sprint_date but I couldn't get the result i wanted.  I have also tried to create a new column which highlights the first instance which i could then use to sum, but again, I couldn't get the DAX to work for me.  Ideally, I would be able to have the sum equivalent of Count (Distinct) in the visual, but failing that, if anyone can help me to tweak my DAX building my summary table, so that I only bring in a single transition per interation for each issue_key & status

[v-lid-msft · Accepted solution]

Hi @chipchidster ,

We can try to use the following dax to meet your requirement:

History_sprint_summary =
SUMMARIZE (
    history,
    history[issue_key],
    history[to_status],
    history[story_points],
    "to_date", MAX ( history[to_date] ),
    "max_sprint_date", MAX ( history[sprint_to_date] )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

Best regards,

[chipchidster]

Thank you @v-lid-msft and apologies for only coming back to this today - it's the first time i have had a chance to make further changes to my dashboard.  With a little tweak, this is now working perfectly.  I have actually changed the MAX to MIN so that it is picking up the first time each issue goes into that status, which for velocity purposes makes more sense.

[amitchandak]

Create a measure like this and add to the table, it will show one rows.

calculate(max(table[story point]),
filter(summarize(table,table[issue_key],"_maxdt",max(table[date]),"_maxGp",max(table[issue_key])),
			table[issue_key]=[_maxGp] && max(table[date]=_maxdt)))

You can use sum, if needed.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin