March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi guys,
I need to create a burnup chart (line-chart) for open issues grouped by project and issue-type. The x-axis represents time (e.g. date) while the y-axis represents the amount of open issues at a date.
My table 'issues' looks like that:
ISSUE_ID | PROJECT_ID | ISSUE_TYPE | CREATED_ON | CLOSED_ON |
whole number | whole number | text | date | date |
I assume, that I have to create a 'time-table' with CALENDAR or CALENDARAUTO and join / summarize / group it somehow with the 'issue' table.
It would be great if you would give me some advice on how I can aggregate those information into a new table in order to display them in a line chart.
Thanks in advance,
Arne
Solved! Go to Solution.
Okay, I think I figured it out by myself.
I created a new table:
open_issues_timeline =
VAR _calendar =
CALENDAR("01/01/2018", MAX(issues[created_on]))
RETURN
CROSSJOIN(
DISTINCT(
SELECTCOLUMNS(issues, "created.on", issues[created_on])
),
DISTINCT(
SELECTCOLUMNS(issues, "tracker.name", fact_project_issues[tracker.name], "project_id", issues[project.id])
)
)
Then I added a new column:
open_issues =
COUNTX(
FILTER(
FILTER(
issues,
issues[tracker.name] = open_issues_timeline[tracker.name] &&
issues[project.id] = open_issues_timeline[project_id]
),
(issues[created_on] <= open_issues_timeline[created.on]) &&
(issues[closed_on] > open_issues_timeline[created.on] || ISBLANK(issues[closed_on]) )
), issues[id]
)
Not a very clean solution; but it works.
Thanks all for reading and replying.
This video will show you https://www.youtube.com/watch?v=2f7dYB1l84g
How to connect Date with the Sales table, quite similar to your scenario, please check
Thanks,
Ritesh
Thank you very much for your reply, @ribisht17 .
I already created a 'Date-Table' using calendarauto. In order to create the 'burnup chart' I assume my 'date' table should look something like that (example):
DATE | ISSUE_TYPE | PROJECT_ID | COUNT_OF_OPEN_ISSUES_AT_GIVEN_DATE |
01.01.2022 | TASK | 1 | 24 |
01.01.2022 | CHANGE REQUEST | 1 | 4 |
01.01.2022 | TASK | 2 | 11 |
.... | .... | .... | .... |
What kind of DAX do I need to write to combine / join both tables with the given columns?
Cheers,
Arne
Okay, I think I figured it out by myself.
I created a new table:
open_issues_timeline =
VAR _calendar =
CALENDAR("01/01/2018", MAX(issues[created_on]))
RETURN
CROSSJOIN(
DISTINCT(
SELECTCOLUMNS(issues, "created.on", issues[created_on])
),
DISTINCT(
SELECTCOLUMNS(issues, "tracker.name", fact_project_issues[tracker.name], "project_id", issues[project.id])
)
)
Then I added a new column:
open_issues =
COUNTX(
FILTER(
FILTER(
issues,
issues[tracker.name] = open_issues_timeline[tracker.name] &&
issues[project.id] = open_issues_timeline[project_id]
),
(issues[created_on] <= open_issues_timeline[created.on]) &&
(issues[closed_on] > open_issues_timeline[created.on] || ISBLANK(issues[closed_on]) )
), issues[id]
)
Not a very clean solution; but it works.
Thanks all for reading and replying.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
27 | |
25 | |
22 | |
16 |