Ranking Issues 2

[smpa01]

@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 function in RANKX like this, but it did not work

I am not sure where it went wrong

By changing this, I can get this. But I am not sure if I should put this code in prod cause I don't understand fully the <row selection> part of WINDOW function and what REL and ABS are internally doing and which would serve my purpose.

//both the following expression returns desired ranking. I don't understand why
Column = 
RANKX (
    WINDOW (
        1,
        ABS,
        1,
        REL,
        SUMMARIZE ( 'Table', 'Table'[yr], 'Table'[val] ),
        ORDERBY ( 'Table'[val] ),,
        PARTITIONBY ( 'Table'[yr] )
    ),
    'Table'[val],
    ,
    ASC
)

Column = 
RANKX (
    WINDOW (
        1,
        ABS,
        -1,
        ABS,
        SUMMARIZE ( 'Table', 'Table'[yr], 'Table'[val] ),
        ORDERBY ( 'Table'[val] ),,
        PARTITIONBY ( 'Table'[yr] )
    ),
    'Table'[val],
    ,
    ASC
)

I want to utilize WINDOW here for rank by partition cause I can relate this to TSQL

Thank you in advance

[AlexisOlson]

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.

[smpa01 · Accepted solution]

@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]
			]
		}
	]
}