Show only most recent row for each ID

[Anonymous]

I have a table that follows this structure

ID_Class	Date of conclusion
1111-Class A	13/04/2022
1111-Class B	20/04/2022
2222-Class A	26/07/2022
2222-Class A	30/09/2022
2222-Class B	14/02/2022
3333-Class A	20/03/2022

The ID_Class column combines the person's unique id and the name of the class they attended. But sometimes there are people who do the same class twice or more but on different dates.
I wanted to create a column of TRUE and FALSE that would bring me the TRUE value whenever each distinct ID_Class is the most recent (since I want to fetch only the most recent courses taken by each person. If a person took the same course twice, I want to see only the most recent of these two courses).

The expected result would be something like this:

ID_Class	Date of conclusion	Most_Recent
1111-Class A	13/04/2022	TRUE
1111-Class B	20/04/2022	TRUE
2222-Class A	26/07/2022	FALSE
2222-Class A	30/09/2022	TRUE
2222-Class B	14/02/2022	TRUE
3333-Class A	20/03/2022	TRUE
3333-Class A	22/01/2022	FALSE

How can I do this?

[smpa01 · Accepted solution]

@Anonymous  you need a measure like following

Measure = 
VAR dt =
    MAX ( 'fact'[dt] )
VAR mxDt =
    CALCULATE ( MAX ( 'fact'[dt] ), ALLEXCEPT ( 'fact', 'fact'[ID_Class] ) )
RETURN
    SWITCH ( TRUE (), dt = mxDt, TRUE (), FALSE () )

@Sahir_Maharaj  DAX rule of thumb,  don't create calculated columns if you can create a measure

[Sahir_Maharaj]

Hi @Anonymous,

Yes, you're correct. The [Date of conclusion] column should be a column in your table, not a measure. In a DAX formula for a calculated column, you can reference other columns in the same table, but you can't reference measures.

So, in this case, you need to make sure that the [Date of conclusion] column is a column in your table and not a measure. Then, you can reference it in your DAX formula to compare the date of conclusion for the current row with the date of conclusion for other rows with the same ID_Class.

Let me know if this works.

[Sahir_Maharaj]

This formula uses a calculated column that creates a variable CurrentIDClass that stores the value of the ID_Class column for the current row. Then, it returns TRUE if the date of conclusion for the current row is equal to the minimum date of conclusion for all rows with the same ID_Class. Otherwise, it returns FALSE.

[Sahir_Maharaj]

You can use the following DAX formula to achieve the desired result:

Most_Recent = VAR CurrentIDClass = MIN('Table'[ID_Class]) RETURN IF(CurrentIDClass = MIN(FILTER('Table', 'Table'[ID_Class] = MIN('Table'[ID_Class]))[Date of conclusion]), TRUE, FALSE)

[Anonymous]

Hi, @Sahir_Maharaj  Thanks for the quick response! 🙂

I didn't quite understand the

[Date of conclusion]

part in your code. Does it use a measure for the date of conclusion? Because when I try to do it here, I can't pull the "Date of conclusion" column of my table, it only lets me bring measures.