Measure to get the last row from a column based on the most recent date

[Anonymous]

Hello people,

I will show the structure of my table so that you can understand better.

Register	Class	Date
000	A	03/05/2020
000	B	03/06/2020
000	A	03/05/2020
000	C	03/07/2020
111	A	03/10/2020
111	A	03/15/2020
111	B	03/08/2020
222	C	03/20/2020
222	A	03/26/2020

As the Register column can be repeated, I created a measure to count all the distinct rows:

_COUNT = COUNTROWS(DISTINCT('Table'[Register]))

This measure is working fine, but now I would like a measure that counts each distinct Register but counting only the most recent row of it (based on the Date column).

Analyzing the table above, the measurement should return the count of just those rows:

Register	Class	Date
000	C	03/07/2020
111	A	03/15/2020
222	A	03/26/2020

Can someone help me please?

[v-lionel-msft]

Hi @Anonymous ,

Try this:

Measure = 
VAR x = 
SUMMARIZE(
    Sheet1,
    Sheet1[Register],
    "Date", MAX(Sheet1[Date])
)
RETURN
COUNTROWS(x)

Best regards,
Lionel Chen

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

[Anonymous]

Thanks for the answer @v-lionel-msft !

I tried this measure and it really only counts distinct Register for each Date, but the Class column does not show the respective class for that Register and this Max (Date).
It is showing random values ​​in the Class column. That's the only problem with this measure, cause is important for me to capture that exact Class of that exact Register (all of that based on the most recent Date of that Register).

[v-lionel-msft]

Hi @Anonymous ,

How about this:

// calculated table
Table = 
VAR x = 
SUMMARIZE(
    Sheet1,
    Sheet1[Register],
    "Date", MAX(Sheet1[Date])
)
RETURN
ADDCOLUMNS(
    x,
    "Class",
    LOOKUPVALUE(
        Sheet1[Class],
        Sheet1[Date], [Date]
    )
)

//measure
Measure 2 = COUNTROWS('Table')

Best regards,
Lionel Chen

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

[Anonymous]

Hi @v-lionel-msft 

When a try to create this new table, an error pops up: "A table with multiple values ​​was provided, with a single value expected"

I will leave a link here for my .pbix file so that you can understand better: 
test2.pbix 

PS: My table's columns has different names from the example table I posted in my question, but:
Register = COD
Class = LEVEL
Date = DATE

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

I took a look at your .pbix file and the error is reported because your data cannot use the LOOKUPVALUE () function.

I wrote another method again, you try again:

1. Creating measure3:

Measure 3 = 
CALCULATE(
    MAX(Sheet3[Date]),
    ALLEXCEPT(
        Sheet3,
        Sheet3[Register]
    )
)

2. Creating measure4

Measure 4 = 
CALCULATE(
    COUNT(Sheet3[Register]),
    FILTER(
        Sheet3,
        Sheet3[Date] = [Measure 3]
    )
)

3. add the two measures to the visual and add filter on the visual:

Best regards,
Lionel Chen

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

[Anonymous]

Dense rank on opportunity id and date and filter 1.

As Column == RANKX(Sales,
			Sales[Category] & Sales[Brand],
			,DESC,Dense
			
		)

[Anonymous]

Sorry, but I didn't quite understand what you did 😞

[amitchandak]

Try like

COUNT = COUNTROWS(DISTINCT('Table'[Register]), filter(all('Table'),'Table'[Date] =max('Table'[Date])))

[Anonymous]

Thanks for the quick response! 🙂

This measure is giving a syntax error right after the     Table'[Register])

The error is "unexpected parameter".