Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

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

Hello people,

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

 

RegisterClassDate
000A03/05/2020
000B03/06/2020
000A03/05/2020
000C03/07/2020
111A03/10/2020
111A03/15/2020
111B03/08/2020
222C03/20/2020
222A03/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:

RegisterClassDate
000C03/07/2020
111A03/15/2020
222A03/26/2020

Can someone help me please?
1 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:

jjj4.PNG

 

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.

View solution in original post

9 REPLIES 9
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try this:

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

fff1.PNG

 

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
Not applicable

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).

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')

 

ggg7.PNG

 

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
Not applicable

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

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:

jjj4.PNG

 

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
Not applicable

Dense rank on opportunity id and date and filter 1.

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

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

amitchandak
Super User
Super User

Try like

COUNT = COUNTROWS(DISTINCT('Table'[Register]), filter(all('Table'),'Table'[Date] =max('Table'[Date])))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Thanks for the quick response! 🙂

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

 

The error is "unexpected parameter".

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.