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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kiranrcse
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

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

22 REPLIES 22
sureshsay2
New Member

does anyone know how to achieve the same for data which is for multiple years. i have date column in YYYYMM format. 

MrRhee
Frequent Visitor

This is great though I am wondering whether it's possible to do this by YEAR & MONTH?
I need a rolling last 1 year that can be filtered by date.

v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft,

 

Quick question how will the DAX need to be changed to account also for the year.

 

Thank you in advance!

 

Hello @v-shex-msft,

 

Quick question how will the DAX need to be changed to account also for the year.

 

Thank you in advance!

 

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.

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello Xiaoxin,

In my case there are one more column is over there which called Service content three different service as given below;

HarshalK_0-1616510753118.png

 

And I need to create Summary Table like:

HarshalK_1-1616510802570.png

 

Could you please help me with this output?

I had tried but still facing issue with Service wise sort out. I'm new into Power BI technology.

 

Kind Regards,

Harshal Kulkarni

 

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

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.

HI @v-shex-msft,

 

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

 

Thank you

HI @v-shex-msft

 

Finally understood it, thank you!

 

You have to do Modeling -> New Table 

 

I'm new to this and didn't understand it.

 

Now all good, thank you!

 

Hi @remi,

 

My formula is works on calculate table, if you drag it to measure or calculate column, it will alert for that error message.

 

Regards,

Xiaoxin Sheng

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

Hi @v-shex-msft,

 

Thank you for your previous replies.

 

Can you tell me how do you change the dax to include multiple years and how would you introduce a category of tickets so that you can filter on those.

 

Thank you in advance.

 

 

Hi @v-shex-msft,

 

Thanks for getting back to me! Not sure how you do that, I've tried to do the small example that you've provided me with and that how I've got the error.

 

Reason for me requesting an example file was that thought that would speed the result. Will you be able to help with it?

 

Kind regards,

Remi Nastase

 

Hi @v-shex-msft,

 

Thanks for getting back to me! Not sure how you do that, I've tried to do the small example that you've provided me with and that how I've got the error.

 

Reason for me requesting an example file was that thought that would speed the result. Will you be able to help with it?

 

Kind regards,

Remi Nastase

 

Hello @v-shex-msft,

 

Thank you for getting back to me!

 

Do you think you can help me with the pbix file to it... not sure what you mean by: "if you drag it to measure or calculate column, it will alert for that error message."

 

Thank you.

 

 

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))

  

I have an excel file and have the first quarterly, Half Yearly , Third Quarter, and annual revenue of multiple years.

I want to display only latest data on cards. But that didn't happened when i choose column in into field area that have specific options of sum, avg, standard deviation, min , max etc 

How can i display actual cell data only specific cell data not whole column. 

Plz anyone guide me

Ayesha_Shabir_0-1664179013631.pngAyesha_Shabir_1-1664179042679.png

 

 

 

Anonymous
Not applicable

Hi, Can you please help to pull the count of resolved tickets which are created in different year and monthly basis.

 

can we get formula to use for differenet years please.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.