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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

  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

[Anonymous]

This would be my end result visual

[Anonymous]

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.

[Anonymous]

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?

[amitchandak]

@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