Dax formula to select a row based on multiple criteria and eliminate duplicates

[mbunchj8]

Hello - I am learning Dax and I am stuck with filtering a table and removing duplicates.  I wish to filter the table to show the latest version of a contract, by contract id.

My table consist of the following:

Contract ID |  Supplier | version | Descr | Begin Date | Expire Date | Max Amount | Line Released Amount | Contract Line number.

I want a Dax formula that will return one line per Contract ID based on the latest version # of the contract.

I am looking for  the formula to return the data I manually highlighted in yellow.  Is this possible?

Thank You -

[FreemanZ · Accepted solution]

hi @mbunchj8 ,

we can do with DAX, but it is more advisible to do with Power Query.

Try with Power Query:

1. sort by version column,

2. then select Contract ID column, remove duplicate rows

[v-bofeng-msft · Accepted solution]

Hi @mbunchj8 ,

I've made a a test for your reference:

1\I assume there is a table

2\Add a new caculate table

FilteredTable = 
    FILTER(
        'YourTableName',
        'YourTableName'[Version] = 
            CALCULATE(
                MAX('YourTableName'[Version]),
                ALLEXCEPT('YourTableName', 'YourTableName'[Contract ID])
            )
    )

Best Regards,

Bof

[Poojara_D12 · Accepted solution]

Hey @mbunchj8 ,

Hope you're doing good!

Here's what I have for you.
DAX:-

LatestContracts =
VAR LatestVersionPerContract =
    SUMMARIZE(
        'Contracts', 
        'Contracts'[Contract ID], 
        "LatestVersion", MAX('Contracts'[Version])
    )
RETURN
    FILTER(
        'Contracts',
        'Contracts'[Version] = LOOKUPVALUE(
            LatestVersionPerContract[LatestVersion],
            LatestVersionPerContract[Contract ID], 'Contracts'[Contract ID]
        )
    )

This formula will return a table with one row per CONTRACT ID showing only the latest version for each contract.

Please let me know if it helps you solve your problem.

Regards,
Poojara.

[mbunchj8]

Poojara_D12  

I am trying to incorporate the formula you have into the Power Querry.  I thinmk I am entering it in the wrong maner.

Where do I insert the formula?

Table QuerryAdvanced Editor

Preview

[v-bofeng-msft · Accepted solution]

Hi @mbunchj8 ,

I've made a a test for your reference:

1\I assume there is a table

2\Add a new caculate table

FilteredTable = 
    FILTER(
        'YourTableName',
        'YourTableName'[Version] = 
            CALCULATE(
                MAX('YourTableName'[Version]),
                ALLEXCEPT('YourTableName', 'YourTableName'[Contract ID])
            )
    )

Best Regards,

Bof

[FreemanZ · Accepted solution]

hi @mbunchj8 ,

we can do with DAX, but it is more advisible to do with Power Query.

Try with Power Query:

1. sort by version column,

2. then select Contract ID column, remove duplicate rows

[mbunchj8]

FreemanZ -

I thought I would try a solution that seemed the easiest first.  When I followed your instructions and sorted the table by Contract Version (Highest to lowest).  It looked good.  I had a contract with version 08 on top of the list.  

I then went to the Contract ID Column and selected "Remove Duplicates".  Thios removed the duplicate contract IDs but it left  only the version 01 contract ID lines.  I did not get the version 08 or any other version besides version 01.

I wish this worked but I am not being left with the latest (highest contract version) as my result.

Thank You -