topic Re: Tickets created after the join date. in DAX Commands and Tips

Tickets created after the join date.

Anonymous — Wed, 27 Jul 2022 14:31:36 GMT

Hi all,

I am trying to find a way I can create a report of tickets created after their joiner date.

I have a master sheet with the following columns needed to create a solution,

Key (which is the ticket number), Created Date, Joiner Date.

This master list includes all tickets like service requests, indicents, and Joiner Forms. Not all tickets have joiner dates, only the tickets that are joiner forms.

Re: Tickets created after the join date.

AUaero — Wed, 27 Jul 2022 16:35:13 GMT

Sample data from your master sheet would be helpful.

Re: Tickets created after the join date.

AUaero — Wed, 27 Jul 2022 16:43:34 GMT

I'm making some assumptions here on what your data actually looks like - mainly assuming that tickets are entered for various customers or users or some other entity.

If your data looks something like the data above, this measure should get you what you want:

# Tickets Created After Joiner Date = VAR tbl = ADDCOLUMNS( SUMMARIZE( Tickets, Tickets[CustomerNum], Tickets[TicketNumber], Tickets[CreatedDate], Tickets[Type], "Joiner_Date", CALCULATE( MINX( Tickets, Tickets[JoinerDate] ), ALL(Tickets), VALUES(tickets[CustomerNum]) ) ), "AfterJoinerFlag", IF( [CreatedDate] >= [Joiner_Date] && [Type] <> "Joiner", 1, 0 ) ) RETURN SUMX( tbl, [AfterJoinerFlag] )

Re: Tickets created after the join date.

Anonymous — Wed, 27 Jul 2022 17:39:20 GMT

Key	Summary	Created	Issue Type	Joiner Start Date	Reporter
SR1-6934	Joiner BOB	7/25/2022	Joiner	8/2/2022	Lisa Helms

Re: Tickets created after the join date.

Anonymous — Wed, 27 Jul 2022 17:48:09 GMT

I do not have a Customer Num, I get an error because of this segmentation

ALL(Tickets),
	    					VALUES(tickets[CustomerNum])

(I used Ticket Number) but since it is not a numberical data type, it gives an error.

Re: Tickets created after the join date.

AUaero — Wed, 27 Jul 2022 18:30:05 GMT

So, a count of all tickets created after "Joiner Start Date" by "Reporter"? Is that the requirement?

Re: Tickets created after the join date.

AUaero — Thu, 28 Jul 2022 01:42:19 GMT

Also, please provide more than one row of sample data.

Re: Tickets created after the join date.

v-jianboli-msft — Mon, 01 Aug 2022 10:09:19 GMT

Hi Anonymous ,

Please try:

First, create a measure:

Flag = IF(MAX('Table'[Joiner Start Date])<MAX('Table'[Created])&&MAX('Table'[Issue Type])="Joiner",1,0)

Then apply it to the visual:

Use the visual level filter:

Final output:

If this is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

Re: Tickets created after the join date.

v-jianboli-msft — Thu, 04 Aug 2022 02:17:28 GMT

Hi Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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