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                 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'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

4 Replies

  • 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..