topic Re: How to Calculate Created, Closed and In progress incidents in DAX in DAX Commands and Tips

How to Calculate Created, Closed and In progress incidents in DAX

Pavani — Tue, 20 Aug 2024 19:33:19 GMT

Hi ,

How to show how many tickets in Created, Closed and In progress tickets in table format , below I attached screen shot of sample one. Created for Submitted Date and Closed for Resolved/ Completed Date .

IPC dump based on Submit Date

Region

Account

Ticket Type

Ticket Number

Priority

Status

Assigned Support Group (if available)

Submit Date

Resolved / Completed Date

Closed Date

Scheduled Start Date

Scheduled End date

Change Success/fail

Coordinator Name

Domain

Problem type

Status reason

Re: How to Calculate Created, Closed and In progress incidents in DAX

Anonymous — Wed, 21 Aug 2024 02:35:35 GMT

Hi Pavani ,

Here are the steps you can follow：

1. Create calculated column.

Ageing = var _if= IF( 'Table'[Status]="Implementation In Progress", DATEDIFF('Table'[Submit Date],TODAY(),DAY), IF( 'Table'[Closed Date]=BLANK(), DATEDIFF('Table'[Submit Date],'Table'[Resolved / Completed Date],DAY), DATEDIFF('Table'[Submit Date],'Table'[Closed Date],DAY) )) return SWITCH( TRUE(), _if>=0&&_if<=7,"0-7Days", _if>7&&_if<=15,"Is>7Days", _if>15&&_if<=30,"Is>15Days", "Is>30Days")

2. Enter data – Table.

3. Because the default Power BI sorting is alphabetical, in order to prevent the appearance of the sorting does not meet your requirements, we can sort according to [Index] to sort.

4. Create measure.

Measure = SWITCH( TRUE(), MAX('Table2'[Group]) = "Created",COUNTX(FILTER('Table','Table'[Submit Date]<>BLANK()),[Domain]), MAX('Table2'[Group]) = "Closed",COUNTX(FILTER('Table','Table'[Status]="Resolved"),[Domain]), MAX('Table2'[Group]) = "In Progress",COUNTX(FILTER('Table','Table'[Status]="Implementation In Progress"),[Domain]), COUNTX( FILTER('Table', 'Table'[Ageing]=MAX('Table2'[Group])),[Ageing]))

5. Result:

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

Re: How to Calculate Created, Closed and In progress incidents in DAX

Pavani — Wed, 21 Aug 2024 14:44:21 GMT

Thank you so much Its working .

Re: How to Calculate Created, Closed and In progress incidents in DAX

Pavani — Wed, 21 Aug 2024 21:01:13 GMT

Hi ,

Thank you so much for your help and one more help below screen shot it is showing total count of P1&P2 tickets but here I need to show MTD One status, Last 7 Das Status and Last 24 Hours status. how we can create. Thank you!

Re: How to Calculate Created, Closed and In progress incidents in DAX

Pavani — Thu, 22 Aug 2024 19:12:20 GMT

Hi , Any help the below request. Thanks!