topic Re: Max funtion in DAX Commands and Tips

Max funtion

Jeffrey_VC — Wed, 10 Aug 2022 10:13:05 GMT

Hello Community,

I'm a newbie who is interesting in Power Bi and the working of DAX.

Now I'm trying to get the penultimate record in a colum. Is there a function in DAX what can help me?

Please can anyone help me in achiving this measure.

Thanks,

Jeffrey

Re: Max funtion

johnt75 — Wed, 10 Aug 2022 10:38:02 GMT

If you have a unique column in the table, like an ID, then you could do something like

Penultimate value = VAR maxID = MAX ( 'Table'[ID] ) VAR val = SELECTCOLUMNS ( CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[ID] ), 'Table'[ID] < maxID ), "@val", 'Table'[Desired column] ) RETURN val

Re: Max funtion

colacan — Wed, 10 Aug 2022 10:40:22 GMT

Hi, You can filter out the row which has minium value then get the minimum value to find penultimate as below.

I hope this helps you.

Thanks

Re: Max funtion

Jeffrey_VC — Wed, 10 Aug 2022 10:57:06 GMT

Hi,

Thank you for your answer.

I have tried:

Penultimate =

var minscore = minx(all('Ledigingen uit Middleware'[Datum/tijd]),

'Ledigingen uit Middleware'[Datum/tijd])

var penul_wo_min = FILTER('Ledigingen uit Middleware',

'Ledigingen uit Middleware'[Datum/tijd] <> minscore)

return MINX(penul_wo_min,'Ledigingen uit Middleware'[Datum/tijd])

The result is not the Penultimate but the first in the dataset:

And I need:

Re: Max funtion

Jeffrey_VC — Wed, 10 Aug 2022 17:43:13 GMT

Hi Johnt75,

Yes that looks like a solution. Thnx!