First Value

[batmanadk]

I´ve edited the post to try to make it clearer. Thanks! 

So, This is a restaurant´s report and what we are trying to find out is what waiter sold the most of X product. So data is generated when a user enters it´s code and enters a product, that´s considered an order, that goes into a TICKET. 

What happens in the restaurant is that every user owns the table they first wait and one else but the cashier or supervisor can enter that table. The problem is that every order has a associated user but tickets only have last ticket user registered.

There are some products that can only be entered by the cashier or supervisor but not by the waiting staff. So when we make a visual with best selling users of one of those products, we want to see ticket users ranking, or ticket owner (meaning waiting staff or the person that first introduced an order into a ticket) but since every order registers a diferent users we always get that the best selling user is a supervisor or cashier.

So this is how the order table looks like.

TicketId	OrderId	User	Product	Revenue
1	1	A	Coke	10
1	2	A	Burger	10
1	3	Z	Special	10
2	4	B	Burger	10
2	5	Z	Special	10
3	6	C	Burger	10
4	7	D	Burger	10
4	8	Z	Special	10
5	9	E	Burger	10
5	10	E	Soda	10
5	11	Z	Special	10
6	12	F	Burger	10
7	13	G	Burger	10
7	14	Z	Special	10

This is what we are trying to achieve something like the ¨TicketUserOwner¨ column

TicketId	OrderId	User	Product	Revenue	TicketUserOwner
1	1	A	Coke	10	A
1	2	A	Burger	10	A
1	3	Z	Special	10	A
2	4	B	Burger	10	B
2	5	Z	Special	10	B
3	6	C	Burger	10	C
4	7	D	Burger	10	D
4	8	Z	Special	10	D
5	9	E	Burger	10	E
5	10	E	Soda	10	E
5	11	Z	Special	10	E
6	12	F	Burger	10	F
7	13	G	Burger	10	G
7	14	Z	Special	10	G

So what is happening is what I´m getting in the upper right table, vs what we need in the lower right table.

I´ve tried some Dax using earlier and lookup 

TicketOwner =

VAR _PrevRow = LOOKUPVALUE(Orders[User],Orders[OrderId],Orders[OrderId]-1)

VAR _Idprevio = LOOKUPVALUE(Orders[TicketId] , Orders[OrderId], Orders[OrderId]-1)

RETURN

IF(

Orders[OrderId] = _Idprevio ,Orders[User],Orders[User])

The attached Pbi file is in the following link:

https://drive.google.com/file/d/10lD88o7-o0vQOdFcS0GHPnvM-oa-L_xT/view?usp=sharing

[v-robertq-msft · Accepted solution]

Hi, @batmanadk 

According to your description and sample data, I think you want to get a column [Ticket User Owner] which is the first User group by Ticket Id, you can try this calculated column:

TicketUserOwner1 =

var _minorderid=

CALCULATE(MIN('Orders'[OrderId]),FILTER('Orders',[TicketId]=EARLIER(Orders[TicketId])))

return

CALCULATE(MIN('Orders'[User]),FILTER('Orders',[OrderId]=_minorderid))

Then place the column into the table chart to replace the original [TicketUserOwner], sort by [Revenue] desc, you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[batmanadk]

Hey @v-robertq-msft ! Thanks a lot for the help! You really saved me! 

[v-robertq-msft]

Hi, @batmanadk 

According to your description, I can roughly understand your expression but I’m confused about what’s the “best selling staff (that is not the supervisor)”, I can not get clear information through the sample table you posted. I also can’t find your sample PBI desktop file. Would you like to take a look and post the sample file and explain your requirement in detail(using some pictures to show the expected output)?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[batmanadk]

I´m getting an error message when trying to reply with all details, I´ve edited the original post, hope it´s clearer. Thnaks a lot