Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Advanced Row_number in DAX

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                 2...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

any solution?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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