Advanced Row_number in DAX

[Anonymous]

Hi everyone,

I need to simulate an SQL code in DAX which uses ROW_NUMBER(),

Here is example data;
pId           pItemId  pItemCode  pDate          pType
4957711  1             3                 2019-02-14  1 
4529194  1             3                 2018-02-14  1
4088750  1             3                 2017-02-08  1
4008149  1             3                 2016-12-31  1
4957713  6             10               2019-02-14  1
4651976  6             10               2018-06-06  1
4088736  6             10               2017-02-08  1

Here is the SQL Code that i want to simulate;

SELECT 
	pID,
	ROW_NUMBER() 
		OVER(PARTITION BY pItemCode 
		     ORDER BY pType DESC, pDate DESC ,pId DESC
			 ) AS priceOrder  
FROM #Price
WHERE pDate <= @Date

@Date parameter is the Maximum Date in the current filter context,

and i define it using;

VAR maxDate = MAX ( 'Date'[DateKey] )

I need to find row_number according to user selections;

For example; if user selects or filters 2018 Year i need a table that returns;

pID           priceOrder
4529194   1
4088750   2
4008149   3
4651976   1
4088736   2

(Just for info; then i will use this on-the-fly calculated table to filter Price table in the model by pID where priceOrder = 1)

i used RANKX but could not simulate Over Clause (partition by and multiple order by columns)

I will be appreciated for any help or suggestions,

Thanks,

[Anonymous]

any solution?

[Anonymous · Accepted solution]

Hi there.

Mate, here's a DAX query (not a measure!) that is equivalent (under some conditions) to your SQL query:

EVALUATE
CALCULATETABLE(
	ADDCOLUMNS(
		MyData,
		
		"priceOrder",
			var __pItemCode = MyData[pItemCode]
			var __pType = MyData[pType]
			var __pDate = MyData[pDate]
			var __pId = MyData[pId]
			return
				COUNTROWS(
					FILTER(
						ALLSELECTED( MyData ),
						MyData[pItemCode] = __pItemCode &&
						MyData[pType] <= __pType &&
						MyData[pDate] <= __pDate &&
						MyData[pId] <= __pId
					)
				)
	),
	-- this is a filter to show that it works correctly
	-- when there is a filter on the date column
	MyData[pDate] <= date(2018,2,14)
)
ORDER BY
	MyData[pItemId],
	MyData[pDate] desc,
	[priceOrder]

I'm not sure what you want to achieve since a DAX measure can only return one value, not a table. You'd have to define exactly what it is you want to return for a given context.

Best

Darek

[Anonymous]

Hi @Anonymous , 

Thank you very much for your answer,

The SQL code is a part of complete code.

The complete code is trying to find the latest item price before the given date using

SELECT *
FROM #Price
WHERE PriceOrder = 1

I may achieve this with modifying your DAX code,

Now, I need to join this on-the-fly calculated table to another transaction table (called SpecialPrice) via pID,

then Return SUM('SpecialPrice'[ItemPrice]) on that joined table.

I will search and make my hands dirty about that 🙂

If you have any advice i will be happy to hear,

Anyway, thanks a lot again..

[Anonymous]

Your SQL is just filtering... You can filter the table that my DAX returns easily using FILTER.