topic Re: Dax formula to select a row based on multiple criteria and eliminate duplicates in DAX Commands and Tips

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

mbunchj8 — Thu, 07 Nov 2024 00:04:19 GMT

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:

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 -

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

FreemanZ — Thu, 07 Nov 2024 00:16:24 GMT

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

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

Anonymous — Thu, 07 Nov 2024 02:36:26 GMT

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

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

Poojara_D12 — Thu, 07 Nov 2024 05:00:35 GMT

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.

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

mbunchj8 — Fri, 15 Nov 2024 19:46:03 GMT

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 -

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

mbunchj8 — Fri, 15 Nov 2024 20:57:43 GMT

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?

Preview