Forum Discussion

smpa01's avatar
smpa01
Community 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 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

 

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

     

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super 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.

  • smpa01's avatar
    smpa01
    Community Champion
    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]
			]
		}
	]
}