topic Re: Highest value formula in DAX Commands and Tips

Highest value formula

georgec96 — Thu, 16 Jun 2022 07:59:51 GMT

Hi All,

I've got the following dataset. I'm trying to find a formula that would help me keep the oldest PO Age value but a distinct value in the can_clear_backorders column.

As an example the first 2 PO Numbers serve the same purpose however i would like to show only the oldest one (based on PO Age column).

Any help much appreciated.

Re: Highest value formula

tamerj1 — Thu, 16 Jun 2022 08:31:36 GMT

Hi georgec96
Please try

Max PO Age = CALCULATE ( MAX ( TableName[PO Age] ), ALLEXCEPT ( TableName, TableName[Desctription] ) )

Re: Highest value formula

georgec96 — Thu, 16 Jun 2022 08:45:23 GMT

Hi tamerj1 ,

Any idea how can i make the table visual not to duplicate the values?

It's still showing the same as before after applying formula you provided.

Re: Highest value formula

tamerj1 — Thu, 16 Jun 2022 08:57:28 GMT

Hi georgec96
Please try

Max PO Age = VAR CurrentPOAge = MAX ( TableName[PO Age] ) VAR MaxPOAge = CALCULATE ( MAX ( TableName[PO Age] ), ALLEXCEPT ( TableName, TableName[Desctription] ) ) RETURN IF ( CurrentPOAge = MaxPOAge, MaxPOAge )

Re: Highest value formula

georgec96 — Thu, 16 Jun 2022 09:03:48 GMT

Hi tamerj1 ,

I am getting the following error

Re: Highest value formula

tamerj1 — Thu, 16 Jun 2022 09:07:47 GMT

georgec96
the last Table should be 'Table'

Re: Highest value formula

georgec96 — Thu, 16 Jun 2022 09:49:16 GMT

Hi tamerj1 , sorry to be a pain but this is not giving me the expected result as you can see.

Is there any way I can create maybe a new table that would give me distinct values in the column Description but keep the highest value in the PO Age column?

Re: Highest value formula

tamerj1 — Thu, 16 Jun 2022 09:50:49 GMT

georgec96
Please create it as a measure (New measure) not a calculated column

Re: Highest value formula

georgec96 — Thu, 16 Jun 2022 09:56:23 GMT

tamerj1 ,

Perfect! Thanks alot that worked!