Get only open tickets

[vaalyushin]

Hi Guys ,

I need your help in something , 

This is my dataset : 

issue_number	status_change_date	current_status	status	creation_date	creation_date - Copy	CriticalLabel
2530	12/7/2021 0:00	Closed	Open	12/7/2021 0:00	12/7/2021 23:22	High
2530	12/7/2021 0:00	Open	Open	12/7/2021 0:00	12/7/2021 23:22	High
2530	12/9/2021 0:00	Closed	Closed	12/7/2021 0:00	12/7/2021 23:22	High
2530	12/9/2021 0:00	Open	Closed	12/7/2021 0:00	12/7/2021 23:22	High
2610	12/22/2021 0:00	Closed	Closed	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Closed	Open	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Closed	Resolved	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Closed	In progress	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Open	Closed	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Open	Open	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Open	Resolved	12/22/2021 0:00	12/22/2021 12:08	Low
2610	12/22/2021 0:00	Open	In progress	12/22/2021 0:00	12/22/2021 12:08	Low
2620	12/23/2021 0:00	Closed	Closed	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Closed	Open	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Closed	Resolved	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Closed	In progress	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Open	Closed	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Open	Open	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Open	Resolved	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Open	In progress	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Resolved	Closed	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Resolved	Open	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Resolved	Resolved	12/23/2021 0:00	12/23/2021 13:24	Low
2620	12/23/2021 0:00	Resolved	In progress	12/23/2021 0:00	12/23/2021 13:24	Low
2641	12/28/2021 0:00	Closed	Open	12/28/2021 0:00	12/28/2021 18:11	Low
2641	12/28/2021 0:00	Open	Open	12/28/2021 0:00	12/28/2021 18:11	Low
2641	12/28/2021 0:00	Waiting	Open	12/28/2021 0:00	12/28/2021 18:11	Low
2641	2/7/2022 0:00	Closed	Closed	12/28/2021 0:00	12/28/2021 18:11	Low
2641	2/7/2022 0:00	Open	Closed	12/28/2021 0:00	12/28/2021 18:11	Low
2641	2/7/2022 0:00	Waiting	Closed	12/28/2021 0:00	12/28/2021 18:11	Low

I need get table with only open tickets. The main column with status is current_status. I tried this dax code:

Open issue = 
FILTER (
         'jira_vulnerability_issues',
          jira_vulnerability_issues[current_status] = "Open" && jira_vulnerability_issues[status_change_date] = [Max Change Date]
    )


Max Change Date = CALCULATE(MAX('jira_vulnerability_issues'[status_change_date]),ALLSELECTED('jira_vulnerability_issues'[status_change_date]))

but it doesn't work

[v-eqin-msft]

Hi @vaalyushin ,

Has methods provided above solved your problem? If so, please kindly Accept helpful replies as the solution. More people will benefit from it.

Below is my simple test:

New Table = 
var _maxDate=MAX('Table'[status_change_date])
return 
FILTER('Table',[current_status]="Open" && [status_change_date]=_maxDate)

Output:

Or if the max_date is a dynamic selection in slicer, please firstly create a date table:

Date Slicer = CALENDAR(MIN('Table'[status_change_date]), MAX('Table'[status_change_date])) 

Then create a flag measure, and apply it to visual-filter pane, set as "is 1":

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aditya_Meshram]

Hi @vaalyushin 
can you try

Open issue = 
FILTER (
         'jira_vulnerability_issues',
          jira_vulnerability_issues[status_change_date] = [Max Change Date],
          jira_vulnerability_issues[current_status] = "Open"
    )

[vaalyushin]

Sorry, i forgot a part of dax measure. my measure is the same, but it doesn't work (i get all tickets with closed state too)

[Aditya_Meshram · Accepted solution]

Okay so what I understand is that you want all the open tickets on the max change date.
Is this the result you want?

You can use 

Open issue = 
var Max_Change_Date = CALCULATE(MAX('jira_vulnerability_issues'[status_change_date]),ALLSELECTED('jira_vulnerability_issues'[status_change_date]))
return
FILTER (
         'jira_vulnerability_issues',
          jira_vulnerability_issues[current_status] = "Open" && jira_vulnerability_issues[status_change_date] = Max_Change_Date
    )

[Greg_Deckler]

@vaalyushin Maybe:

Table =
  VAR __Table = SUMMARIZE('Table',[issue_number],"last_date",MAX('Table'[status_change_date]))
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "current_status",
          VAR __issue = [issue_number]
          VAR __date = [last_date]
        RETURN
          MAXX(FILTER('Table',[issue_number] = __issue && [status_change_date] = __date),[current_status)
RETURN
  FILTER(__Table1,[current_status] = "Open")