Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago
Solved

Ranking Issues 2

CNENFRNL  AlexisOlson jeffrey_wang    I am trying to get a RANK column with WINDOW function. The end result is this with RANKX and without WINDOW function     I tried using WINDOW fu...
Need Help
  • smpa01's avatar
    smpa01
    3 years ago

    @jeffrey_wang  I can see see the evaluation context of REL in DAX DEbug output with EVALUATEANDLOG

     

    This is simply awesome

     

    {
	"expression": "WINDOW (\n                -1,\n                REL,\n                0,\n                REL,\n                SUMMARIZE ( tbl, tbl[yr], tbl[val] ),\n                ORDERBY ( tbl[val] ),\n                KEEP,\n                PARTITIONBY ( tbl[yr] )\n            )",
	"inputs": ["'tbl'[yr]", "'tbl'[val]"],
	"outputs": ["'tbl'[yr]", "'tbl'[val]"],
	"data": [
		{
			"input": [2022, 100],
			"rowCount": 1,
			"output": [
				[2022, 100]
			]
		},
		{
			"input": [2022, 200],
			"rowCount": 2,
			"output": [
				[2022, 100],
				[2022, 200]
			]
		},
		{
			"input": [2022, 300],
			"rowCount": 2,
			"output": [
				[2022, 200],
				[2022, 300]
			]
		},
		{
			"input": [2023, -200],
			"rowCount": 1,
			"output": [
				[2023, -200]
			]
		},
		{
			"input": [2023, -100],
			"rowCount": 2,
			"output": [
				[2023, -200],
				[2023, -100]
			]
		}
	]
}

     

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

One easy way to see what's going on with a table is to use TOCSV (hover to see line with line breaks).

 

The other version is not the same:

 

From https://dax.guide/window/:

 

  • If FromType is REL, the number of rows to go back (negative value) or forward (positive value) from the current row to get the first row in the window.
  • If FromType is ABS, and From is positive, then it’s the position of the start of the window from beginning of the partition. Indexing is 1-based. For example, 1 means window starts from the beginning of the partition. If From is negative, then it’s the position of the start of the window from the end of the partition. -1 means the last row in the partition.

So, 1, ABS, -1, ABS covers the whole window whereas 1, ABS, 1, REL goes from the start of the window through the the next row (compared to the current row) in the window.