get the productName ordered first most often

[power_roboter]

Hi experts,

my table looks like

productId , productName, orderId, orderDateTime, customerId

I would like to use a dax measure to find the productName, that was

most often (number of orderId) ordered first (orderDateTime) by the customers (customerId).

Could you please help me?

[amitchandak]

@power_roboter ,

For first order date you create a new column Rank.
ordered first = rankx(filter(Table, [customerId] =earlier([customerId])),[orderDateTime],,asc,dense)
Filter on Rank =1

most often order you have create measure rank
ordered first = rankx(filter(allselected(Table[customerId], Table[productName]), [customerId] =max([customerId])),calculate(count(Table[orderId])),,desc,dense)

you can filter rank =1 , to use topN

Refer for TOPN - https://www.youtube.com/watch?v=QIVEFp-QiOk

Column Rank -https://www.youtube.com/watch?v=wDS_Vi4r9I4

measure Rank -https://www.youtube.com/watch?v=DZb_6j6WuZ0

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[power_roboter]

Hi amitchandak,

this was very helpful, with internalCustomerRank I get the order rank for every customerId.

VAR orderTable =
SELECTCOLUMNS (
orders,
"customerId", orders[customerId],
"internalCustomerRank", rankx(filter(orders, orders[customerId] = EARLIER(orders[customerId])),orders[created],,asc,dense),

"created",orders[created],
"productName",orders[productName]
)

Now, I have this table in my measure and want to calculate for every productName, how often it has the [internalCustomerRank] = 1.

My approach is:

Distinct(

SELECTCOLUMNS (
orderTable,

"productName", [productName],

"numberOfOrdersWithRank_1", ?

))

How can I calculate the "numberOfOrdersWithRank_1" for every productName? I can't get any further here...