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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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])))
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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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