Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm currently trying to create extensive Power BI report for Azure DevOps work items. I got direct access with SQL to analytics tables and views. I have already checked the official documentation here, but most of the sample reports for historical data pretty much refers to WorkItemSnapshot table.
I also did some experiments with WorkItemSnapshot but that would force me to go with daily level reporting and I would be also interested of weekly or sprint level history. Also even with direct SQL call and some WHERE limitations it was really slow for longer periods (we got lot of stuff on db). I thought that since that table is anyway some sort of CROSSJOIN between dates and WorkItemRevision table, probably I could directly fetch the full table on use some DAX magic to achieve much more performant and flexible end result. Only thing is that I don't have enough of that magic and that is why I'm here.
Some measures I'm looking for:
To start with total efforts we would need sum of effort for last revision of each work item in current filter context, say single date. Probably we haven't modified work item on that date so we would need to fetch last revision before that date.
Here is simplified example of WorkItemRevision data for someone who is not familiar with Azure DevOps datamodel:
Hopefully you could throw in at least some comments which direction I should look for.
Hi did you manage to find a work around?
Sorry. No. Probably compromized something but cannot remember any more.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
15 |
User | Count |
---|---|
38 | |
20 | |
19 | |
16 | |
10 |