Time in board COLUMN...

[dcorbin-wintell]

New to "M".... struggling...

Trying to use Add a time-in-state measure to a Power BI report - Azure DevOps | Microsoft Docs as a basis, but NOT "state", rather "Board column"....

My immediate problem is one calculation I don't see what is wrong...


CALCULATE (
MAX ( '#"PP04-WorkItemBoardSnapshot"'[DateValue] ),
ALLEXCEPT ( '#"PP04-WorkItemBoardSnapshot'", '#"PP04-WorkItemBoardSnapshot"'[WorkItemId] ),
'#"PP04-WorkItemBoardSnapshot"'[DateValue] < EARLIER ( '#"PP04-WorkItemBoardSnapshot"'[DateValue] )
)

I have tried every combination of quotes, and #  (dont thik I should need either since no spaces...but...

[Iryna_K]

Try Time in State for Azure DevOps extension as an alternative https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure .

It's not the solution for Power BI, but it's easier to use. Report generation is automatic. You only need to select time or work item intervals.

I hope you find this helpful solution.

[FlowViz]

@dcorbin-wintell I have this information in the latest version of the free Power BI template FlowViz - https://github.com/nbrown02/FlowViz 

[Anonymous]

Hi @dcorbin-wintell ,

Looks like you want to create a measure? Sadly you can't use Earlier() function in a measure. You could try use < selectedvalue([datevalue]) instead. In addition, you want DAX help, please show some sample data and expected to us.

Best Regards,

Jay

[dcorbin-wintell]

Jay,

Thanks for the response.  The material I am trying to replicate (conceptually) is in the linked article. The DAta is a standard Microsoft DevOps Analytics Table (apparently board column changes are not stored in real time).

[dcorbin-wintell]

really the EARLIER that has me stumped...

[YukiK · Accepted solution]

Gotcha. Try using calculated column instead of a measure. Looks like that's what the article you mentioned is demonstrating. That may resolve the issue.

If this helps, please give it a thumbs up!

[dcorbin-wintell]

Thanks, that got me past that first one....  Not it looks like the actual datapoints when a value has changed (for doing hisotry/trends) is not in the analytics at all 🙄. Instead there is a nightly snapshot, which does nothing to capture the flow (multiple chnages) within a day....

[YukiK]

You're trying to implement DAX in Power Query, which is not possible. DAX and M language is different in that the latter is used in Power Query (data transformation steps) and DAX is used after loading your tables.

For your case, use that DAX code by right clicking on a table after loading data into the data model, and choose "New Measure". And paste your code in the editor.

Hope that makes sense!

[dcorbin-wintell]

Thanks for the quick response.... That was where I started... And this is what that yields...