cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kiranrcse Frequent Visitor
Frequent Visitor

dax - how to count open tickets

For the same sample data, is there a way to get this output. I am looking for Total Active tickets during that month.

Expected report output:

 Total ActiveOpenedClosed
Jan220
Feb312
Mar210
Apr530
May612

Sample Data:

SROpened dateClosed Date
T11-Jan-16 
T21-Jan-161-Feb-16
T32-Feb-164-Feb-16
T43-Mar-16 
T54-Apr-16 
T64-Apr-16 
T74-Apr-161-May-16
T81-May-168-May-16

 

Thanks in Advance,

Kiran.

1 ACCEPTED SOLUTION

Accepted Solutions
kiranrcse Frequent Visitor
Frequent Visitor

Re: dax - how to count open tickets

Thanks @v-shex-msft. I made a small change to the code snippet you provided and it worked excellent. 

I changed this :

Sheet1[Closed Date].[MonthNo]>=EARLIER([Month])
Summary Table = 
SELECTCOLUMNS(
    ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Sheet1[Closed Date].[MonthNo]),VALUES(Sheet1[Opened date].[MonthNo])),"Month",[Closed Date].[MonthNo])),[Month]<>BLANK()),
        "Open",COUNTAX(FILTER(ALL(Sheet1),[Opened date].[MonthNo]=EARLIER([Month])||AND([Opened date].[MonthNo]=EARLIER([Month]),Sheet1[Closed Date]>MAX(Sheet1[Opened date]))),[SR]),
        "Close",COUNTAX(FILTER(ALL(Sheet1),[Closed Date].[MonthNo]=EARLIER([Month])),[SR]),
        "Total",COUNTAX(FILTER(ALL(Sheet1),Sheet1[Opened date].[MonthNo]<=EARLIER([Month])&&OR(Sheet1[Closed Date].[MonthNo]>=EARLIER([Month]),Sheet1[Closed Date]=BLANK())),[SR])),
                "Month",FORMAT(DATE(2016,[Month],1),"MMMM"),
                "Close",if([Close]>0,[Close],0),
                "Open",if([Open]>0,[Open],0),
                "Total",if([Total]>0,[Total],0))

  

View solution in original post

18 REPLIES 18
Community Support Team
Community Support Team

Re: dax - how to count open tickets

Hi @kiranrcse,

 

You can refer to below formula to get the summary table about tickets:

 

Summary Table =
SELECTCOLUMNS(
    ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Sheet1[Closed Date].[Month]),VALUES(Sheet1[Opened date].[Month])),"Month",[Closed Date].[Month])),[Month]<>BLANK()),
        "Open",COUNTAX(FILTER(ALL(Sheet1),[Opened date].[Month]=EARLIER([Month])),[SR]),
        "Close",COUNTAX(FILTER(ALL(Sheet1),[Closed Date].[Month]=EARLIER([Month])),[SR])),
                "Month",[Month],
                "Open",if([Open]<>BLANK(),[Open],0),
                "Close",if([Close]<>BLANK(),[Close],0),
                "Total",if([Close]+[Open]<>BLANK(),[Open]+[Close],0))

 

Capture.PNG

 

Notice, above formula works when year of date fields have the same value.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
kiranrcse Frequent Visitor
Frequent Visitor

Re: dax - how to count open tickets

Thanks @v-shex-msft for the reply. 

In the total column I am looking for all tickets which might be active in that month. 

For example: For Month of April 3 new tickes have been opened, 1 ticket from Jan and 1 ticket from March are also still active/open.

The total count for the month of April is 5.

 

Is there a way to get that.

Community Support Team
Community Support Team

Re: dax - how to count open tickets

Hi @kiranrcse,

 

According to your description, I modified the formula, you can try to use below formula if it suitable for your requirement.

 

Summary Table = 
SELECTCOLUMNS(
    ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Sheet1[Closed Date].[MonthNo]),VALUES(Sheet1[Opened date].[MonthNo])),"Month",[Closed Date].[MonthNo])),[Month]<>BLANK()),
        "Open",COUNTAX(FILTER(ALL(Sheet1),[Opened date].[MonthNo]=EARLIER([Month])||AND([Opened date].[MonthNo]=EARLIER([Month]),Sheet1[Closed Date]>MAX(Sheet1[Opened date]))),[SR]),
        "Close",COUNTAX(FILTER(ALL(Sheet1),[Closed Date].[MonthNo]=EARLIER([Month])),[SR]),
        "Total",COUNTAX(FILTER(ALL(Sheet1),Sheet1[Opened date].[MonthNo]<=EARLIER([Month])&&OR(Sheet1[Closed Date].[MonthNo]>EARLIER([Month]),Sheet1[Closed Date]=BLANK())),[SR])),
                "Month",FORMAT(DATE(2016,[Month],1),"MMMM"),
                "Close",if([Close]>0,[Close],0),
                "Open",if([Open]>0,[Open],0),
                "Total",if([Total]>0,[Total],0))

 

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: dax - how to count open tickets

You also find a very nice solution to that problem here (especially if you run into performance problems): http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




kiranrcse Frequent Visitor
Frequent Visitor

Re: dax - how to count open tickets

Thanks @v-shex-msft. I made a small change to the code snippet you provided and it worked excellent. 

I changed this :

Sheet1[Closed Date].[MonthNo]>=EARLIER([Month])
Summary Table = 
SELECTCOLUMNS(
    ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Sheet1[Closed Date].[MonthNo]),VALUES(Sheet1[Opened date].[MonthNo])),"Month",[Closed Date].[MonthNo])),[Month]<>BLANK()),
        "Open",COUNTAX(FILTER(ALL(Sheet1),[Opened date].[MonthNo]=EARLIER([Month])||AND([Opened date].[MonthNo]=EARLIER([Month]),Sheet1[Closed Date]>MAX(Sheet1[Opened date]))),[SR]),
        "Close",COUNTAX(FILTER(ALL(Sheet1),[Closed Date].[MonthNo]=EARLIER([Month])),[SR]),
        "Total",COUNTAX(FILTER(ALL(Sheet1),Sheet1[Opened date].[MonthNo]<=EARLIER([Month])&&OR(Sheet1[Closed Date].[MonthNo]>=EARLIER([Month]),Sheet1[Closed Date]=BLANK())),[SR])),
                "Month",FORMAT(DATE(2016,[Month],1),"MMMM"),
                "Close",if([Close]>0,[Close],0),
                "Open",if([Open]>0,[Open],0),
                "Total",if([Total]>0,[Total],0))

  

View solution in original post

remi Member
Member

Re: dax - how to count open tickets

hello @kiranrcse,

 

can you help me with a .pbix example of what you've done?

I have some trouble understanding how to adapt the code to my data 

 

 

https://community.powerbi.com/t5/Desktop/Calculate-the-remaining-tasks-and-percentage-of-the-complet...

 

kind regards

 

 

remi Member
Member

Re: dax - how to count open tickets

hi @v-shex-msft,

 

Do you think you can help me with the files for this formula, I'm trying to go thru it but right now I've stopped at [SR].

 

Looking forward to your help.

 

Kind regards

remi Member
Member

Re: dax - how to count open tickets

Hello, @v-shex-msft  and @kiranrcse,

 

Hope both of you is doing well.

 

Tried again to use the code shared, but now I run into the following alarm:

 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Summary Table 3 =
SELECTCOLUMNS(ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES('Sheet1'[Closed Date].[MonthNo]),VALUES('Sheet1'[Opened date].[MonthNo])),"Month",'Sheet1'[Closed Date].[MonthNo])),[Month]<>BLANK()),
"Open",COUNTAX(FILTER(ALL('Sheet1'),'Sheet1'[Opened date].[MonthNo]=EARLIER([Month])||AND([Opened date].[MonthNo]=EARLIER([Month]),'Sheet1'[Closed Date]>MAX(Sheet1[Opened date]))),'Sheet1'[SR]),
"Close",COUNTAX(FILTER(ALL('Sheet1'),[Closed Date].[MonthNo]=EARLIER([Month])),'Sheet1'[SR]),
"Total",COUNTAX(FILTER(ALL('Sheet1'),'Sheet1'[Opened date].[MonthNo]<=EARLIER([Month])&&OR('Sheet1'[Closed Date].[MonthNo]>=EARLIER([Month]),'Sheet1'[Closed Date]=BLANK())),'Sheet1'[SR])),
"Month",FORMAT(DATE(2016,[Month],1),"MMMM"),
"Close",if([Close]>0,[Close],0),
"Open",if([Open]>0,[Open],0),
"Total",if([Total]>0,[Total],0))

 

 

 

Can you tell me where it fails?

 

Kind regards.

remi Member
Member

Re: dax - how to count open tickets

HI @v-shex-msft,

 

Can you point me to what I'm doing wrong?

 

Thank you

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,742 guests
Please welcome our newest community members: