topic Re: How to tak 3 recent row from table in DAX in DAX Commands and Tips

How to tak 3 recent row from table in DAX

adii — Thu, 11 Jan 2024 11:55:32 GMT

Hello ,

I'm looking solve my problem .

I have a table with column A with datetime

Column A
2024-01-11 10:54:55

2024-01-11 10:54:33

2024-01-11 10:51:00

2024-01-11 10:50:23

Column B

123

175

155

122

125

i would like recive table ( Column A and B) with first 4 row based on Datetime (Ascending)

which function I should use ?
I thought about RANKX , but that function rankx needs measure

thanks

Re: How to tak 3 recent row from table in DAX

lbendlin — Thu, 11 Jan 2024 14:29:32 GMT

use the WINDOW function with relative addressing from -3 to -1

Re: How to tak 3 recent row from table in DAX

gmachowiak — Thu, 11 Jan 2024 15:19:49 GMT

I'd use TOPN fucntion:

TOPN(

'Table',

'Table'[Column1],

ASC

)

Re: How to tak 3 recent row from table in DAX

adii — Thu, 11 Jan 2024 21:50:19 GMT

Doesn't work , it's multiply values, should be scalar

Re: How to tak 3 recent row from table in DAX

adii — Thu, 11 Jan 2024 21:50:35 GMT

Doesn't work

Re: How to tak 3 recent row from table in DAX

adii — Thu, 11 Jan 2024 21:52:00 GMT

What you think about add column with numer increment ? Start from 1 to 4

Re: How to tak 3 recent row from table in DAX

Anonymous — Fri, 12 Jan 2024 04:43:36 GMT

Hi adii ,

Here are the steps you can follow：

1. Create calculated column.

Rank = RANKX( 'Table','Table'[ColumnA],,DESC,Dense)

2. Create calculated table.

Table 2 = FILTER( 'Table', [Rank]<=3)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to tak 3 recent row from table in DAX

adii — Fri, 12 Jan 2024 11:14:07 GMT

Perfect it's working thanks .
so can you help me in a little more tricky example . Now I need do the same like above but I got extra column C with date (year-month-day) and a would like make ranking for each day separately.

? I'n trying use

calculate( Rank, All(table[column C]) but doesn't work ..

Re: How to tak 3 recent row from table in DAX

Anonymous — Mon, 15 Jan 2024 10:23:59 GMT

Hi adii ,

Are you referring to finding the ordering of [column A] based on the grouping of [column C]?

You can use the following calculated columns:

Rank = RANKX( FILTER( ALL('Table'),'Table'[ColumnC]=EARLIER('Table'[ColumnC])),'Table'[ColumnA],,DESC,Dense)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly