topic Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date in DAX Commands and Tips

DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Wed, 13 Jul 2022 17:51:51 GMT

Hi,

I have a bulk export of tickets with new hire name, task number, closed date, created date, start date, ect.

I am looking to show "number of users completed by the start date". To determine this, the new hire, who has many tickets assigned to them, has to have all tickets with a "closed date". If they have any tickets without a "close date" that means they still have a ticket open which means they were "not completed by start date".

I imagine this would be a measure using DAX expression. Any insight?

Thanks

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

amitchandak — Thu, 14 Jul 2022 03:31:04 GMT

Anonymous , Refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Or the attached files after signature

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Mon, 18 Jul 2022 05:49:37 GMT

Hi Anonymous ,

Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards
Community Support Team _ Polly

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

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Mon, 18 Jul 2022 14:37:37 GMT

Below is a sample. I would like to say this Dragon 1 (Displayed as 1 is a count of 1 New Hire Name) has all their tasks closed therefore they are a completed new hire and their tasks are all closed and 1 person is remaining which is Dragon 2 has tasks still open.

Request	RITM number	Number	Created	Closed	New Hire Name	Start Date	State
REQ8745216	RITM0380443	TASK0455408	7/4/2022 07:02:33 AM	7/11/2022 08:48:38 AM	Dragon 1	7/18/2022	Closed
REQ8745216	RITM0380443	TASK0455250	6/29/2022 06:28:36 AM	7/4/2022 06:10:07 AM	Dragon 1	7/13/2022	Closed
REQ8546215	RITM0381472	TASK0455248	7/13/2022 10:33:33 PM		Dragon 2	7/13/2022	Open
REQ8546215	RITM0381472	TASK0455246	7/13/2022 10:33:32 PM		Dragon 2	7/13/2022	Open
REQ8546215	RITM0381472	TASK0455245	7/13/2022 10:33:32 PM		Dragon 2	7/13/2022	Open

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Mon, 18 Jul 2022 14:41:16 GMT

This would be my end result visual

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Tue, 19 Jul 2022 02:41:08 GMT

Hi Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create two measures.

completed new hires = CALCULATE ( DISTINCTCOUNT ( 'Table'[New Hire Name] ), FILTER ( ALL ( 'Table' ), 'Table'[State] = "Open" ) )

remaining = CALCULATE ( DISTINCTCOUNT ( 'Table'[New Hire Name] ), ALLSELECTED ( 'Table' ) )

If I have misunderstood your menaing, please provide more detail with your desired output.

Best Regards
Community Support Team _ Polly

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

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Tue, 19 Jul 2022 17:03:10 GMT

The end result would be 1 completed new hire and 1 still remaining because their tasks do not have a closed date.

Instead of a measure using the open states, can we use a expression based off of closed dates?

Re: DAX to represent a "complete" user, based on if they have all tickets with a closed date

Anonymous — Wed, 20 Jul 2022 02:24:23 GMT

Hi Anonymous ,

I have modify my answer.

Please refer to it to see if it helps you.

Create a measure.

remaining = VAR _1 = CALCULATE ( DISTINCTCOUNT ( 'Table'[New Hire Name] ), ALLSELECTED ( 'Table' ) ) VAR _2 = CALCULATE ( DISTINCTCOUNT ( 'Table'[RITM number] ), FILTER ( ALL ( 'Table' ), 'Table'[State] = "Open" && 'Table'[Closed] = BLANK () ) ) RETURN _1 - _2

If I have misunderstood your menaing, please provide more details with your desired output.

Best Regards
Community Support Team _ Polly

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