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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Harish85
Helper I
Helper I

required Most recent date data based on Vgbel and vbeln columns

Hi Team,

 

I am trying to get most recent date data based up on the below columns,In VGBEL column from last 2nd row is having recent date,so only that column should be seen in the result. we should get last two rows in the result.

Harish85_0-1737545823798.png

 

Regards,

Harish.

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

Hi @Harish85   - Create a new calculated table as like below and replace with your table name as per your model.

rajendraongole1_0-1737547020182.png

 

LastTwoRows =
VAR RankedData =
    ADDCOLUMNS(
        dataino,
        "RowRank",
        RANKX(
            FILTER(
                dataino,
                dataino[VGBEL] = EARLIER(dataino[VGBEL])
            ),
            dataino[UPDATE_DATE_TIME],
            , -- Skip the value for expression, as we are ranking directly by UPDATE_DATE_TIME
            DESC -- Specify descending order
        )
    )
RETURN
    FILTER(RankedData, [RowRank] <= 2)
 
Please check and revert back if any issues




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

5 REPLIES 5
v-linhuizh-msft
Community Support
Community Support

Thanks for the replies from danextian and rajendraongole1.

 

Hi @Harish85 ,

 

If you want to take the rajendraongole1's method, you need to create a calculated table instead of a measure:

vlinhuizhmsft_0-1737704412143.png

 

The method provided by danextian requires the creation of a calculated column, which is then filtered on the visual:

vlinhuizhmsft_1-1737704552361.png

 

If you need a measure, you can also refer to the following method:

1. Create a measure:

Measure = IF(CALCULATE(MAX('Table'[UPDATE_DATE_TIME]),ALLEXCEPT('Table','Table'[VGBEL]))=MAX('Table'[UPDATE_DATE_TIME]),1,0)

 

2. Use this measure to filter the visual:

vlinhuizhmsft_2-1737704745796.png

 

3. Results obtained using simple data:

vlinhuizhmsft_4-1737705456466.png

Best Regards,
Zhu

 

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

rajendraongole1
Super User
Super User

Hi @Harish85   - Create a new calculated table as like below and replace with your table name as per your model.

rajendraongole1_0-1737547020182.png

 

LastTwoRows =
VAR RankedData =
    ADDCOLUMNS(
        dataino,
        "RowRank",
        RANKX(
            FILTER(
                dataino,
                dataino[VGBEL] = EARLIER(dataino[VGBEL])
            ),
            dataino[UPDATE_DATE_TIME],
            , -- Skip the value for expression, as we are ranking directly by UPDATE_DATE_TIME
            DESC -- Specify descending order
        )
    )
RETURN
    FILTER(RankedData, [RowRank] <= 2)
 
Please check and revert back if any issues




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Harish85_0-1737554751025.png

Getting above error, and one more thing there will be thousands of data, it will apply to all rows right.

 

 

Measure = VAR RankedData =
    ADDCOLUMNS(
        VBAP_E,
        "RowRank",
        RANKX(
            FILTER(
                VBAP_E,
                VBAP_E[VGBEL] = EARLIER(VBAP_E[VGBEL])
            ),
            VBAP_E[UPDATE_DATE_TIME],
            , -- Skip the value for expression, as we are ranking directly by UPDATE_DATE_TIME
            DESC -- Specify descending order
        )
    )
RETURN
    FILTER(RankedData, [RowRank] <= 2)
 
getting below error
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
danextian
Super User
Super User

Hi @Harish85 

Create this calculated column

Latest Records = 
-- This measure determines if a row represents the latest record for each 'vgbel'.
IF(
    CALCULATE(
        -- Find the maximum value of the 'updated_date_time' column for the current 'vgbel'
        MAX('table'[updated_date_time]),
        -- Modify the filter context to include only the rows with the same 'vgbel'
        ALLEXCEPT('table', 'table'[vgbel])
    )
    -- Compare the maximum 'updated_date_time' for the current 'vgbel' 
    -- with the 'updated_date_time' of the current row
    = 'table'[updated_date_time],
    -- If the comparison is TRUE, return 1
    1,
    -- Otherwise, return 0
    0
)

This check for the updated_date_time date time for each disintct vgbel and compare it against the current row's updated_date_time. If they're the same this will return 1 else 0. Filter your visual to 1.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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