Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register 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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |