Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tcburge3
Helper I
Helper I

Counting Active Ticket numbers by group

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 NumberStateCreatedResolvedGroup
1Resolved11/06/2311/06/23Group1
2Resolved11/06/2311/07/23Group2
3Resolved11/07/2311/07/23Group1
4

New

11/08/23 Group2
5New11/09/23 Group3

I created a table in powerbi that is basically a list of these created and resolved tickets that looks like this.

TicketAttributeDate
1Created11/06/23
1Resolved11/06/23
2Created11/06/23
2Resolved11/07/23
3Created11/07/23
3Resolved11/07/23
4Created11/08/23
5Created11/09/23

The purpose of that was to build the following visual.

tcburge3_0-1699549145328.png

 

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.

tcburge3_2-1699551037955.png

 

 

 

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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.

tcburge3
Helper I
Helper I

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors