How to return Distinct rows based on multiple values

Hey,

I created a little example, that uses the sample data + one extra ticketnumber.

What you achieve by the windowing function ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) is achieved by this calculated column, except the filtering this comes a little later

IsMaxUpdatedTime = 
IF('TicketStatus'[UpdatedTime] = 
CALCULATE(
    MAX('TicketStatus'[UpdatedTime]),
    ALLEXCEPT('TicketStatus',TicketStatus[Ticket Number],TicketStatus[UpdatedDate])
),"Yes", "No")

This column can now be used as a slicer, or in any other visual If you do not want to expose this column to your user (just hide it from report view) , you can also use this in any CALCULATE statement like this CALCULATE(<expression>,  'TicketStatus'[UpdatedTime] = "yes")

Because I'm calculating a column I have to be aware, that there is just a ROW CONTEXT. This means that just using

MAX('TicketStatus'[UpdatedTime])

will always return the value of the current row. Knowing this I have to introduce a FILTER CONTEXT this is done by encapsulating the the MAX( into a CALCULATE(. Still MAX would return the value of the current row, because the current row works as a filter, but now I'm able to use the power of CALCULATE to expand the current FILTER CONTEXT  the current row. This is done by

ALLEXCEPT('TicketStatus',TicketStatus[Ticket Number],TicketStatus[UpdatedDate])

Using this functions removes the filter from all the columns of the table, except [Ticket Number] and the date column (this column is necessary as you already mentioned, this column just contains the date without any time information.

Now I'm using the result of the calculate statement to check if the returned date value equals the date value of the current row. If this is the case I know that I found the latest datetime for ticketnumber and date.

Now I'm able to use this column in my report.

In the sample report I used the column as a slicer, and turned the interaction with the bar chart off by

1. mark the slicer and switch "Edit Interaction" on

2. then I disabled the filter interaction of the bar chart

3 finally i added my new calculated column as an visual level filter to the bar chart to ensure that the bar chart visual becomes independent from the slicer and just uses the rows that are flagged ismaxupdatedtime = yes

Done :-)

Hope this helps