topic Re: Distinct Count by filter in DAX Commands and Tips

Distinct Count by filter

vaalyushin — Fri, 18 Feb 2022 11:39:07 GMT

Good afternoon!

I need to get the number of open and closed applications. At the same time, the application may have a history of its states (open, in operation, closed, resolved)

My dataset:

ticket_number	current_status	created_date	status_date_change
1	open	1.01.2022	1.01.2022
1	in progress	2.01.2022	2.01.2022
2	open	2.02.2022	2.02.2022
2	in progress	2.02.2022	2.02.2022
2	closed	2.02.2022	2.02.2022
3	open	3.02.2023	3.02.2023

Re: Distinct Count by filter

Russell-PBI — Fri, 18 Feb 2022 12:40:21 GMT

Could you create a flag in your dataset to identify the latest status for each ticket based on created_date and a sort of the current_status, then filter for this in your report or within a measure?

ticket_number	current_status	created_date	status_date_change	latest_status_flag
1	open	1.01.2022	1.01.2022	0
1	in progress	2.01.2022	2.01.2022	1
2	open	2.02.2022	2.02.2022	0
2	in progress	2.02.2022	2.02.2022	0
2	closed	2.02.2022	2.02.2022	1
3	open	3.02.2023	3.02.2023	1

CALCULATE(COUNTROWS(YourDataset), latest_status_flag = 1)

Might be easier than trying to serve it all up in a measure.

EDIT: COUNTROWS.

Re: Distinct Count by filter

tamerj1 — Fri, 18 Feb 2022 12:38:45 GMT

Hi vaalyushin
You have only options for the satus which are: Open, then it will become In Progress (In Operation) then it will be either Closed or Resolved. Am I right? Do you have other options?

Re: Distinct Count by filter

vaalyushin — Fri, 18 Feb 2022 12:40:07 GMT

Yes, you are right

Re: Distinct Count by filter

vaalyushin — Fri, 18 Feb 2022 12:55:43 GMT

I didn't fully understand what you meant. now I do it through the measure, but naturally I get the wrong result

CountOpenTickets = CALCULATE ( DISTINCTCOUNT('tickets'[issue_number] ), FILTER ( 'tickets', 'tickets'[current_status] = "Open" && 'tickets'[creation_date] = MAX ('jira_vulnerability_issues'[creation_date]) ) )

Re: Distinct Count by filter

Russell-PBI — Fri, 18 Feb 2022 13:08:48 GMT

This probably isn't the most streamlined solution as I don't use PowerQuery for transformations, but here is what I came up with: https://drive.google.com/file/d/16XYzwjHyqbhbhbSodc03yXOPorGpj_V3/view?usp=sharing

Effectively, it works out the latest created_date per ticket, the latest current_status based on what will most likely come after each status, then uses the combination of those two bits of information to decide which record is the latest per ticket. If that doesn't work because status will change back and forth, then you might need to consider adding date and time into your dataset rather than just date.

Re: Distinct Count by filter

tamerj1 — Fri, 18 Feb 2022 13:15:12 GMT

Hi vaalyushin
Here is the sample file with the solution https://www.dropbox.com/t/fZytnjZorSijq226
A new calculated column has to be created:

Status = VAR CurrentTicket = Tickets[ticket_number] VAR CurrentTicketAllAvailableStatuses = FILTER ( Tickets, Tickets[ticket_number] = CurrentTicket ) VAR Result = SWITCH ( TRUE, CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "Resolved" ), "Resolved", CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "Closed" ), "Closed", CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "Resolved" ), "Resolved", CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "In Progress" ), "In Progress", "Open" ) RETURN Result

The measure is a simple distinct count

Ticket Count = COUNTROWS ( DISTINCT ( Tickets[ticket_number] ) )

You can use the newly caculated column in rows or slicers. And the measure will give you the distinct count as per your selection.

Re: Distinct Count by filter

vaalyushin — Mon, 21 Feb 2022 07:28:12 GMT

Thank you, It's nice, but i think it can be resolve with measure

Re: Distinct Count by filter

Russell-PBI — Mon, 21 Feb 2022 12:33:49 GMT

tamerj1's posted solution works with a calculated column and measure, so no messing about with PowerQuery. I'd say that's a good solution for you.