Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello, I am working on a solution that looks at active ticket numbers for different groups in my organization. My original Data set looks like this.
Ticket Number | State | Created | Resolved | Group |
1 | Resolved | 11/06/23 | 11/06/23 | Group1 |
2 | Resolved | 11/06/23 | 11/07/23 | Group2 |
3 | Resolved | 11/07/23 | 11/07/23 | Group1 |
4 | New | 11/08/23 | Group2 | |
5 | New | 11/09/23 | Group3 |
I created a table in powerbi that is basically a list of these created and resolved tickets that looks like this.
Ticket | Attribute | Date |
1 | Created | 11/06/23 |
1 | Resolved | 11/06/23 |
2 | Created | 11/06/23 |
2 | Resolved | 11/07/23 |
3 | Created | 11/07/23 |
3 | Resolved | 11/07/23 |
4 | Created | 11/08/23 |
5 | Created | 11/09/23 |
The purpose of that was to build the following visual.
Problem Statement: Now what I am hoping to do is to create a stacked bar chart with a line that shows each of my group's active tickets that sum up to the "Active Ticket Trend" Line (The line that shows the cumulative number of active tickets) for each day. So that I know the breakdown of active tickets per group for every day.
I currently am able to show today's active ticket number per group by using some simple filtering (shown below), but I want to be able to do this for every day in the past that has active tickets.
I tried creating a relationship between the ticket numbers in my 2 tables and adding the [group] column, but that is not working.
I would really appreciate any help to achieve this solution. Please let me know if you have any questions or want some additional explanation. I have a link to the powerBI document and excel source data I am using for this.
Here is a link to the powerbi file and source data.
Link: https://drive.google.com/drive/folders/1kWEEEP8iIiPmDeXkye3W38EsWMrAZvt0?usp=sharing
I greatly appreciate your time and help - thank you!
-Tim
Solved! Go to Solution.
Hi @tcburge3 ,
You can try using the following dax.
Measure 1 =
var _columnnumber=
SELECTCOLUMNS(
FILTER(ALL('Ticket Trend'),
'Ticket Trend'[Date]=MAX('Ticket Trend'[Date])),"Number",'Ticket Trend'[Number])
var _count=
COUNTX(
FILTER(ALLSELECTED('Current Incidents'),
'Current Incidents'[Number] in _columnnumber&&'Current Incidents'[xActive]=1),
'Current Incidents'[Number])
return
_count
Measure 2 =
SUMX(
FILTER(ALLSELECTED('Ticket Trend'),
'Ticket Trend'[Date]<=MAX('Ticket Trend'[Date]) ),[Measure 1])
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tcburge3 ,
You can try using the following dax.
Measure 1 =
var _columnnumber=
SELECTCOLUMNS(
FILTER(ALL('Ticket Trend'),
'Ticket Trend'[Date]=MAX('Ticket Trend'[Date])),"Number",'Ticket Trend'[Number])
var _count=
COUNTX(
FILTER(ALLSELECTED('Current Incidents'),
'Current Incidents'[Number] in _columnnumber&&'Current Incidents'[xActive]=1),
'Current Incidents'[Number])
return
_count
Measure 2 =
SUMX(
FILTER(ALLSELECTED('Ticket Trend'),
'Ticket Trend'[Date]<=MAX('Ticket Trend'[Date]) ),[Measure 1])
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I added my powerbi file and the source data in the link. Is there anything else I need to include to get some help on this?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |