Not only first and last, but also second.. or Nth.

[zudar]

Hi all,

I'm having trouble with a matrix table and could use your help.

What I want is pretty simple and this is all coming from a single table in my model: I have one dimension on the columns and another one on the rows. Let's say dates on the columns and sales people on the rows. As values, I want to put the order ID's that my sales people sold on a single day.

I found that when I drag in the Order ID, PowerBI is giving me the option to choose 'first' or 'last' (so I can display two Order ID's by dragging in 'Order ID' twice and choose 'first' and 'last'), but I want to show, let's say, up to 10 Order ID's per person per day (more than 10 is pretty much impossible). Another problem I faced is that when using the 'first' and 'last' options is that when there is only one Order ID, it is displayed twice, because it's both the first and last Order ID for that person on that day.

What is the best approach using DAX to accomplish this?

I want to show something like this, with Order ID's in ascending order per person per day:

		1-11-2020	2-11-2020
John	First Order	4918	4926
	Second Order	4919	4928
	Third Order	4923
William	First Order	4917	4925
	Second Order	4920	4927
	Third Order		4929
Charles	First Order	4921	4930
	Second Order	4922	4931
	Third Order	4924

[Anonymous · Accepted solution]

[Nth Order] =
var __nth = 1 // change this to any n you want
// Get OrderID's from the 1st highest
// in the context to the __nth highest.
var __orderIDs =
    TOPN(__nth,
        DISTINCT( Orders[OrderID] ),
        Orders[OrderID],
        desc
    )
// Get the order id with the lowest
// id. This could be the __nth one
// you're looking for.
var __nthOrderID =
    MINX(
        __orderIDs,
        Orders[OrderID]
    )
// If there are exactly __nth
// orders, it means you've got
// the __nth order in there
// you're after.
var __shouldDisplay =
    COUNTROWS( __orderIds ) = __nth
var __output =
    if( __shouldDisplay,
        __nthOrderID
    )
return
    __output

[amitchandak]

@zudar , Create a rank column like

Rankx(filter(Table, [customer] =earlier([customer]) && [date] =earlier([date])),[Order ID],asc)

[zudar]

@amitchandak 

And then probaby something like:

Third Order = MAXX(
    TOPN( 1, FILTER (
        Orders,
        Orders[Rank] = 3
    )),Orders[Order ID])

etc.. ?