Increment using dense rank in power BI DAX

[sruthicb]

Hello,

I have 4 columns . I want the below mentioned output. but I don't know what do we call this output. an denserank? I don't think it is the case because dense rank gives different results than I wanted.
Can anyone tell what approach we need to follow in DAX to get this desire output. I thought I can use dense rank but not in my case. Thank you

[bhanu_gautam]

@sruthicb , Try using

DAX
Rank =
VAR CurrentScore = 'Table'[score]
VAR CurrentName = 'Table'[name]
VAR CurrentGroup = 'Table'[Group]
RETURN
RANKX(
FILTER(
'Table',
'Table'[Group] = CurrentGroup && 'Table'[name] = CurrentName
),
'Table'[score],
,
DESC,
DENSE
)

[sruthicb]

This doesnot work for me.
where ever there is score 90, it is giving me the same Rank. 
It is working same as Rank. but I want the result as mentioned in the screenshot

[Deku]

In your table there is no column giving a ordering, without this your request is not possible

[sruthicb]

Hi @Deku , Can you please suggest which one to use to get the desired result in the screenshot? Thank you for your help. 

[Deku · Accepted solution]

This measure gives the required ranking

Rank = 
var streaks =
	ADDCOLUMNS(
		ALLSELECTED( 'Table'[Category], 'Table'[date], 'Table'[Diffference] )
		,"@StreakChanged",
			IF(
			COALESCE(
				SELECTCOLUMNS(
					CALCULATETABLE(
						OFFSET( 
							-1, 
							ALLSELECTED( 'Table'[Category], 'Table'[date], 'Table'[Diffference] ),
							ORDERBY( 'Table'[date], ASC ),
							PARTITIONBY( 'Table'[Category] )
						)
					)
					,'Table'[Diffference]
				)
				,'Table'[Diffference]
			) = 'Table'[Diffference]
			, 1, 0
		)
		,"@RowNum",
			ROWNUMBER(
				ALLSELECTED( 'Table'[Category], 'Table'[date], 'Table'[Diffference] ),
				ORDERBY( 'Table'[date], ASC ),
				PARTITIONBY( 'Table'[Category] )
			)
	)
var islands = 
	ADDCOLUMNS(
		streaks,
		"@rank",
			var dt = 'Table'[date]
			var cat = 'Table'[Category]
			return
			( [@RowNum] - SUMX( FILTER( streaks, 'Table'[date] <= dt && 'Table'[Category] = cat), [@StreakChanged] ) ) + 1
	) 
RETURN
SELECTCOLUMNS(
    FILTER(
        islands
        ,var dt = SELECTEDVALUE( 'Table'[date] )
        var cat = SELECTEDVALUE( 'Table'[Category] )
        return
        'Table'[date] = dt && 'Table'[Category] = cat
    )
    ,[@rank]
)

[sruthicb]

Hi @Deku  thanks for the response. 

[Deku]

Just have to add EARLIER to out the outer row context

rnk = 
var streaks =
	ADDCOLUMNS(
		ALLSELECTED( 'Table'[Category], 'Table'[date], 'Table'[Diffference] )
		,"@StreakChanged",
			IF(
			COALESCE(
				SELECTCOLUMNS(
					CALCULATETABLE(
						OFFSET( 
							-1, 
							ALLSELECTED( 'Table'[Category], 'Table'[date], 'Table'[Diffference] ),
							ORDERBY( 'Table'[date], ASC ),
							PARTITIONBY( 'Table'[Category] )
						)
					)
					,'Table'[Diffference]
				)
				,'Table'[Diffference]
			) = 'Table'[Diffference]
			, 1, 0
		)
		,"@RowNum",
			ROWNUMBER(
				ALLSELECTED( 'Table'[Category], 'Table'[date], 'Table'[Diffference] ),
				ORDERBY( 'Table'[date], ASC ),
				PARTITIONBY( 'Table'[Category] )
			)
	)
var islands = 
	ADDCOLUMNS(
		streaks,
		"@rank",
			var dt = 'Table'[date]
			var cat = 'Table'[Category]
			return
			( [@RowNum] - SUMX( FILTER( streaks, 'Table'[date] <= dt && 'Table'[Category] = cat), [@StreakChanged] ) ) + 1
	)
var dt = 'Table'[date]
RETURN
SELECTCOLUMNS(
    FILTER(
        islands
        ,var dt = EARLIER( 'Table'[date] )
        var cat = EARLIER( 'Table'[Category] )
        return
        'Table'[date] = dt && 'Table'[Category] = cat 
    )
    ,[@rank]
)

[sruthicb]

what id I add Index column?

[Deku]

A date or some kind of index

[sruthicb]

I changed my input and added a date column. here instead of score , now it is difference column.the main date column is DATE