How do I get the last item related to a specific value

[Maryam_Pour]

Hi,
I have a matrix table that contains two columns.
A reference no. is the same for all mails in each mail thread, and the mail numbers change every time somebody replay or forwards the mail.

The view that I,m trying to create is this one:

So the Reference no. and the last email in the thread will be in one row, plus the count of all emails related to this reference number.

I've added Email numbers under the Values for Matrix table and selected the LAST, but it doesn't show me the last email number.
What do I do?

I hope you can help me to solve this issue.

[HotChilli]

"when there are many emails in the mail thread, I get another mail number but not the last one."

can you provide the reference numbers for an email which shows this please.  Just enough to illustrate this please (i.e. not 500 references!)

[Fowmy]

@Maryam_Pour 

Use two measures.


LAST EMAIL NO = MAX ( TABLENAME[EAMIL NUMBER] )
COUNT OF EMAILS = COUNTA( TABLENAME[EAMIL NUMBER] )

[Maryam_Pour]

I have a big table with many reference numbers and many emails related to each of these reference numbers.
I'm not seeing how I can get the last email for each reference number in your solution.

[HotChilli]

"it doesn't show me the last email number" - what does it show?

[Maryam_Pour]

In some cases, when there are a few emails in the mail thread, I get the last email and reference number in one row. But when there are many emails in the mail thread, I get another mail number but not the last one.

[v-chenwuz-msft]

Hi @Maryam_Pour ,

Maybe you do some filters to this page where the matrix table is by slicer or filter pane.You can use ALLEXCPET() to to remove other filter context on this measure. Some code like the following.

Last =
CALCULATE(
    LASTNONBLANK( 'Table'[Email Number], 0 ),
    ALLEXCEPT( 'Table', 'Table'[Reference Number] )
)

If this does not work well, creating a index column maybe another choice.Please refer code below.

Last_index =
VAR _index =
    CALCULATE(
        MAX( 'Table'[Index] ),
        ALL( 'Table' ),
        'Table'[Reference Number] = SELECTEDVALUE( 'Table'[Reference Number] )
    )
RETURN
    CALCULATE( MAX( 'Table'[Email Number] ), 'Table'[Index] = _index )

Best Regards

Community Support Team _ chenwu zhu

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