How to Return the Latest Revisions for Each Document

[Anonymous]

Hello everyone,

I'm working on a Power BI project, and I have an extensive data table containing information about engineering documents. Each document has multiple revisions, and I would like to create a table that displays not only the latest revisions for each document but also the first occurrence when a document appears with a specific revision. There's an important detail to note: with each document list submission, the records repeat, so it's essential to return not only the highest revision but also the first time the document appears with a particular revision.

I've tried some approaches using measures and calculated columns, but I haven't been able to achieve the desired result.

Here are the details of my table:

• Table Name: [f_LDs]
• Column that identifies when the document list was sent: [f_LDs[Dt. LD]]
• Column that identifies the documents: [f_LDs[Nº Alunorte]]
• Column representing the revision number: [LDs[Revisão]]

In the exemple above i'd like to be returned only the last row info

Could someone please guide me on how to accomplish this task in Power BI? What DAX formula should I use, or what steps should I follow in Power Query to achieve this goal?

Any help or suggestions will be greatly appreciated. Thank you in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below

Flag = 
VAR _seldate =
    SELECTEDVALUE ( 'f_LDs'[Dt. LD] )
VAR _rev =
    SELECTEDVALUE ( 'f_LDs'[Revisão] )
VAR _doc =
    SELECTEDVALUE ( 'f_LDs'[Nº Alunorte] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'f_LDs'[Dt. LD] ),
        FILTER ( ALLSELECTED ( 'f_LDs' ), 'f_LDs'[Nº Alunorte] = _doc )
    )
VAR _predate =
    CALCULATE (
        MAX ( 'f_LDs'[Dt. LD] ),
        FILTER (
            ALLSELECTED ( 'f_LDs' ),
            'f_LDs'[Nº Alunorte] = _doc
                && 'f_LDs'[Dt. LD] < _maxdate
                && NOT ( ISBLANK ( 'f_LDs'[Dt. LD] ) )
        )
    )
RETURN
    IF ( _seldate IN { _predate, _maxdate }, 1, 0 )

Or create a calculated column:

Column = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'f_LDs'[Dt. LD] ),
        FILTER ( 'f_LDs', 'f_LDs'[Nº Alunorte] = EARLIER ( 'f_LDs'[Nº Alunorte] ) )
    )
VAR _predate =
    CALCULATE (
        MAX ( 'f_LDs'[Dt. LD] ),
        FILTER (
            'f_LDs',
            'f_LDs'[Nº Alunorte] = EARLIER ( 'f_LDs'[Nº Alunorte] )
                && 'f_LDs'[Dt. LD] < _maxdate
                && NOT ( ISBLANK ( 'f_LDs'[Dt. LD] ) )
        )
    )
RETURN
    IF ( 'f_LDs'[Dt. LD] IN { _predate, _maxdate }, 1, 0 )

2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)

Best Regards