Page 1 of 1

Ranking a column in one table against related occurrences

Posted: Wed Sep 15, 2021 6:51 pm
by Nerdio
I have two tables, one which is a list of SKUs, the other is a list of orders for those SKUs. Something like this.

SKU | Description
S001 | Item #1
S002 | Item #2
S003 | Item #3

Order No. | SKU | Quantity
O0001 | S001 | 2
O0002 | S001 | 3
O0002 | S003 | 1
O0003 | S002 | 5
O0004 | S001 | 2
O0004 | S003 | 3

I would like to create a Measure, so I can Rank the 'popularity' of each SKU. A result of something like this, based on the above.

SKU | Rank
S001 | 1
S003 | 2
S002 | 3

As a start I have tried this to count the number of orders for each SKU. (Just as a stepping stone to my final requirement)

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

NumOrders:=
VAR thisSKU = ItemTable[SKU]
RETURN
CALCULATE(DISTINCTCOUNT(OrderTable[Order No]), FILTER(OrderTable, OrderTable[SKU]=thisSKU))


This throws up an error because there is no aggregation on 'thisSKU'. My approach is clearly wrong, but being a bit of a DAX novice I am lacking some knowledge here. If someone could help me with the calculation of the number of orders for each SKU I should then be able to rank them.

TIA

Re: Ranking a column in one table against related occurrences  [Solved]

Posted: Fri Sep 17, 2021 5:14 pm
by Nerdio
I have a solution.

NumOrders:=DISTINCTCOUNT(Orders[Order Num])

then

ItemRank:=IF([NumOrders]>0,RANKX(ALLSELECTED(Items[SKU]),[NumOrders]),BLANK())

Re: Ranking a column in one table against related occurrences

Posted: Tue Sep 21, 2021 5:17 am
by Trinder
I often use Windowed Functions in the ORDER BY clause. It's super useful when I'm looking for examples in my data of frequently occurring / high number of events. For example...

SELECT *
FROM dbo.Episodes
ORDER BY COUNT(*) OVER (PARTITION BY PatientID) desc
Which will return all the rows in table Episodes, ordered by the patient with the most Episodes recorded.