Display the last entry to a log based on a ticket ID and a Key ID

[Anonymous]

Hello Power BI community,

I hope someone can help.

I have a table with some log data for a ticket system, the log data has several fields with a key_id, and a date time stamp. I'm trying to dispaly in a visual the last entry based on the ticket id and the key id of the field completed. Any ideas are welcome. 

Thank you

Miguel

[v-shex-msft]

Hi @Anonymous ,

You can create a table visual with all fields you needed, then use following measure on visual level filter to filter matched records:

measure =
VAR _lastDate =
    CALCULATE (
        MAX ( Table[Datetime] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Ticket ID] ),
        VALUES ( Table[key ID] )
    )
VAR _currDate =
    MAX ( Table[Datetime] )
RETURN
    IF ( _currDate = _lastDate, "Y", "N" )

If above not help, please share some sample data for test, it is hard to coding formula without any detail information.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello @v-shex-msft ,

First of all thank you for your prompt reply and sorry for not answering earlier, I was out on a business trip and had limited access to email and computer.

What you suggested only gives me a Y or N value on the visual. I'm looking to show a value from the table, let me share some content to better explain.

From below table I am looking to display the last entry for each combination of Ticket_ID & Key_ID, based on the Ticket_ID.

So for ticekt_id 20063, I want to display the value for Key_ID 52 that has the last entry that is the value for row with id of 94952, and for the same ticket_id the value for key_id 53 with the last entry which is in row with id 94953. In the same maner I want to show for ticket_id 20066 and key_id 52 the value  for the row with ID 94618, which is the last entry done, and so on with the entries in red and underlined below.

id	journal_id	Key_ID	value	Ticket_ID	created_on
92664	46257	52	Last interface to be migrated	20063	6/11/19 15:56
92665	46257	53	Checking if all teams are ready for migration.	20063	6/11/19 15:56
91719	45802	53	Continue to monitor progress of migration.	20063	6/4/19 15:09
94953	47333	53	Next step depends on outcome of migration.	20063	6/25/19 16:28
94952	47333	52	Migration failed	20063	6/25/19 16:28
91718	45802	52	New migration date is 6/20/19.	20063	6/4/19 15:09
92044	45969	52	No updates this week	20066	6/6/19 13:10
93594	46689	52	No updates this week	20066	6/18/19 11:16
94618	47160	52	No updates this week	20066	6/25/19 10:08
91387	45677	52	Checking for the applicable solution to phase out equipment	20184	6/3/19 19:46
92287	46091	52	Team checked but unable to confirm an applicable solution to phase out Equipment	20184	6/10/19 18:19
93412	46612	52	Team checked but unable to confirm an applicable solution to phase out Equipment	20184	6/17/19 19:30
94548	47124	52	Equipment has been phased out successfully	20184	6/24/19 23:05
96505	48054	52	Equipment has been phased out successfully	20184	7/8/19 21:55
88597	44216	53	Awaiting feedback from investigation	20184	5/14/19 21:55
90562	45267	53	Awaiting feedback from investigation	20184	5/28/19 13:54
91388	45677	53	Awaiting feedback from investigation	20184	6/3/19 19:46
92288	46091	53	Await for further guides from subject mater expert	20184	6/10/19 18:19
93413	46612	53	Await for further guides from subject mater expert or manager	20184	6/17/19 19:30
94549	47124	53	Await for guidelines for follow-up cleanup	20184	6/24/19 23:05
95706	47683	53	Await for guidelines for follow-up cleanup	20184	7/2/19 0:11
96506	48054	53	Clean up Completed	20184	7/8/19 21:55

Thank you for your prompt reply.

[v-shex-msft]

HI @Anonymous ,

You can use following calculated column formula to mark last date based on 'key id' and 'ticket id', then you can add filter on new column to display last date:

IsLast =
IF (
    [created_on]
        = CALCULATE (
            MAX ( Table1[created_on] ),
            FILTER (
                ALL ( Table1 ),
                [Ticket_ID] = EARLIER ( Table1[Ticket_ID] )
                    && [Key_ID] = EARLIER ( Table1[Key_ID] )
            )
        ),
    "Y"
)

Regards,

Xiaoxin Sheng