Rank vs Rankx

[smpa01]

@AlexisOlson 

I’m experimenting with the RANK window function vs. the traditional RANKX pattern. While I understand that RANK is now the recommended approach by SQLBI for performance and clarity, I’m finding it much more "high-maintenance" than RANKX when it comes to changing visual layouts.

The Scenario: I have a standard Star Schema: fact connected to dim_id, dim_name, and dim_name. I want to rank IDs by sales, partitioned by Period.

The DDL is following, sorry I can't upload pbix; run the following in pbi TMDL editor to build the model automatically (in case you did not know)

createOrReplace

	model Model
		culture: en-US
		defaultPowerBIDataSourceVersion: powerBI_V3
		discourageImplicitMeasures
		sourceQueryCulture: en-CA
		dataAccessOptions
			legacyRedirects
			returnErrorValuesAsNull

		table fact
			lineageTag: 7e4f30e9-189e-49b7-b252-1b517f1a2bd2

			column id
				dataType: string
				lineageTag: fb72d9f6-ea0d-4dff-bf90-21e994716067
				summarizeBy: none
				sourceColumn: id

				annotation SummarizationSetBy = Automatic

			column name
				dataType: string
				lineageTag: ca8f4448-7975-4fcc-b6f2-234d046c1085
				summarizeBy: none
				sourceColumn: name

				annotation SummarizationSetBy = Automatic

			column period
				dataType: string
				lineageTag: f1fdab22-c25e-4523-bc3e-47eb20ebfe46
				summarizeBy: none
				sourceColumn: period

				annotation SummarizationSetBy = Automatic

			partition fact = m
				mode: import
				source =
						let
						    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykyJrzAwVNJRSgRiIwMjU10gL1aHShJGREgYAQWTsBlFpAROo3BKGCMkjIGCydjsQJUwwiWB06hBL4EzSIaIRCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t, period = _t]),
						    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"name", type text}})
						in
						    #"Changed Type"

			annotation PBI_NavigationStepName = Navigation

			annotation PBI_ResultType = Table

		table dim_id
			lineageTag: e271e2dd-af9d-43c7-80d2-4ba66f04bcd4

			column id
				dataType: string
				lineageTag: e0be058e-9cf6-404c-8249-b3b9e4a2c442
				summarizeBy: none
				sourceColumn: id

				annotation SummarizationSetBy = Automatic

			partition dim_id = m
				mode: import
				source =
						let
						    Source = fact,
						    #"Removed Columns" = Table.RemoveColumns(Source,{"name", "period"}),
						    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
						in
						    #"Removed Duplicates"

			annotation PBI_NavigationStepName = Navigation

			annotation PBI_ResultType = Table

		table dim_name
			lineageTag: bac9cf23-16c1-408c-8253-dd5292b63aae

			column name
				dataType: string
				lineageTag: 111479f9-d44c-4491-a2a2-f7e14647b69b
				summarizeBy: none
				sourceColumn: name

				annotation SummarizationSetBy = Automatic

			partition dim_name = m
				mode: import
				source =
						let
						    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitVBZhkBWUk4WcZAVrJSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t]),
						    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"name", type text}}),
						    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"id"}),
						    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
						in
						    #"Removed Duplicates"

			annotation PBI_NavigationStepName = Navigation

			annotation PBI_ResultType = Table

		table Measure
			lineageTag: 46d928cc-cf11-44d5-91bb-3b4c7de7e04b

			measure fact_sum =
					
					COUNTROWS ( 'fact' )
				formatString: 0
				lineageTag: a4c0995e-d5e6-4f12-82be-5626119cd023

			measure dax_RANKX =
					
					VAR _partitionBy =
					    MAX ( 'dim_period'[period] )
					VAR base =
					    RANKX (
					        FILTER (
					            ALLSELECTED ( 'Fact' ),
					            RELATED ( 'dim_period'[period] ) = _partitionBy
					        ),
					        [fact_sum],
					        ,
					        DESC,
					        DENSE
					    )
					VAR finale =
					    IF ( [fact_sum] <> BLANK (), base )
					RETURN
					    finale
				formatString: 0
				lineageTag: fff7b564-4a80-4ce6-99b4-fd5be2e5e1d1

			measure dax_RANK =
					
					RANK (
					    DENSE,
					    SUMMARIZE (
					        ALLSELECTED ( 'fact' ),
					        dim_period[period],
					        dim_name[name],
					        dim_id[id]
					    ),
					    ORDERBY ( [fact_sum], DESC ),
					    PARTITIONBY ( dim_period[period] )
					)
				formatString: 0
				lineageTag: df4fdc0b-08d5-4e8d-a9ac-1de136ae3baa

			column Value
				formatString: 0
				lineageTag: 4e8bb7fc-1315-4cbb-ba5d-0725058aa551
				summarizeBy: sum
				isNameInferred
				sourceColumn: [Value]

				annotation SummarizationSetBy = Automatic

			partition Measure = calculated
				mode: import
				source = { BLANK() }

			annotation PBI_Id = a7199e205d9a4c0b80b332a6fa59a7b2

		table dim_period
			lineageTag: f9948e1c-e03e-4f2a-980d-baae8c3844c9

			column period
				dataType: string
				lineageTag: c843a3ec-a8ec-4472-a072-33bf366ba87c
				summarizeBy: none
				sourceColumn: period

				annotation SummarizationSetBy = Automatic

			partition dim_period = m
				mode: import
				source =
						let
						    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYiMDI1NdA0OlWB1yxYxwihkB+UloegmLYdOLTcwYLGYM5CejmYcqZoRFDKg3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t, period = _t]),
						    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"name", type text}}),
						    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"period"}),
						    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
						in
						    #"Removed Duplicates"

			annotation PBI_NavigationStepName = Navigation

			annotation PBI_ResultType = Table

		table VIZ1
			lineageTag: ceb287bb-7781-410d-904c-5b99063516b4

			column period
				lineageTag: d5f39002-1cc9-4b83-b45f-69d2fb834341
				summarizeBy: none
				isNameInferred
				sourceColumn: dim_period[period]
				columnOrigin: dim_period.period

				annotation SummarizationSetBy = Automatic

			column id
				lineageTag: 7ad2fff4-8e7c-4a11-8c93-1fa66c9432df
				summarizeBy: none
				isNameInferred
				sourceColumn: dim_id[id]
				columnOrigin: dim_id.id

				annotation SummarizationSetBy = Automatic

			column name
				lineageTag: e5c0302b-0980-4d79-b027-d4dfc7118d1a
				summarizeBy: none
				isNameInferred
				sourceColumn: dim_name[name]
				columnOrigin: dim_name.name

				annotation SummarizationSetBy = Automatic

			column fact_sum
				formatString: 0
				lineageTag: 8df8c4c3-7fad-4e41-b384-70df6940205c
				summarizeBy: sum
				isNameInferred
				sourceColumn: [fact_sum]

				annotation SummarizationSetBy = Automatic

			column dax_RANKX
				formatString: 0
				lineageTag: b9aa96af-e8d6-48f1-947a-d728d0720cca
				summarizeBy: sum
				isNameInferred
				sourceColumn: [dax_RANKX]

				annotation SummarizationSetBy = Automatic

			column dax_RANK
				formatString: 0
				lineageTag: cba44745-f1bd-4f8d-b7b5-9e4b6a8453f5
				summarizeBy: sum
				isNameInferred
				sourceColumn: [dax_RANK]

				annotation SummarizationSetBy = Automatic

			partition VIZ1 = calculated
				mode: import
				source =
						
						SUMMARIZECOLUMNS(
						    'dim_period'[period],
						    'dim_id'[id],
						    'dim_name'[name],
						    "fact_sum", 'Measure'[fact_sum],
						    "dax_RANKX", 'Measure'[dax_RANKX],
						    "dax_RANK", 'Measure'[dax_RANK]
						)

			annotation PBI_Id = f4e2a0b2e7874ea4abd06b665e0558bd

		table VIZ2
			lineageTag: 96271aeb-44b4-4567-b5ce-018eb780828f

			column period
				lineageTag: 7998a156-bfcb-4198-b370-66eacf1834f8
				summarizeBy: none
				isNameInferred
				sourceColumn: dim_period[period]
				columnOrigin: dim_period.period

				annotation SummarizationSetBy = Automatic

			column id
				lineageTag: 1d6e1ab0-6f63-4f6d-8ec8-bc050225fd9b
				summarizeBy: none
				isNameInferred
				sourceColumn: dim_id[id]
				columnOrigin: dim_id.id

				annotation SummarizationSetBy = Automatic

			column fact_sum
				formatString: 0
				lineageTag: cbbfa9fd-064d-449d-b888-4fa62039aaac
				summarizeBy: sum
				isNameInferred
				sourceColumn: [fact_sum]

				annotation SummarizationSetBy = Automatic

			column dax_RANKX
				formatString: 0
				lineageTag: e431462f-6f61-4c3b-a328-cf3ad914ebc6
				summarizeBy: sum
				isNameInferred
				sourceColumn: [dax_RANKX]

				annotation SummarizationSetBy = Automatic

			column dax_RANK
				formatString: 0
				lineageTag: cf0352fb-9107-4987-9e12-672917fc21dd
				summarizeBy: sum
				isNameInferred
				sourceColumn: [dax_RANK]

				annotation SummarizationSetBy = Automatic

			partition VIZ2 = calculated
				mode: import
				source =
						
						SUMMARIZECOLUMNS(
						    'dim_period'[period],
						    'dim_id'[id],
						    "fact_sum", 'Measure'[fact_sum],
						    "dax_RANKX", 'Measure'[dax_RANKX],
						    "dax_RANK", 'Measure'[dax_RANK]
						)

			annotation PBI_Id = 8ed995774a664527bb25c36983f3a9a6

		relationship ff074a0e-60a9-e173-627f-64aa06a5b81c
			fromColumn: fact.id
			toColumn: dim_id.id

		relationship 6e915dcb-3ba0-cb33-bee6-bb8985d999d2
			fromColumn: fact.name
			toColumn: dim_name.name

		relationship AutoDetected_8bcc2a27-e364-4ced-9e58-42389ba8b4d3
			fromColumn: fact.period
			toColumn: dim_period.period

		cultureInfo en-US

			linguisticMetadata = {"Version":"1.0.0","Language":"en-US"}
				contentType: json

		annotation __PBI_TimeIntelligenceEnabled = 0

		annotation PBI_QueryOrder = ["fact","dim_id","dim_name","dim_period"]

		annotation PBI_ProTooling = ["TMDLView_Desktop"]

		annotation __TEdtr = 1

The Issue:

• RANKX works in both Viz1 (Period, ID, Name) and Viz2 (Period, ID). I don't have to change the code when I pull name out of the visual; it just "works" because it iterates the fact table.

• RANK fails in Viz2. Since dim_name[name] is explicitly called in the SUMMARIZE relation of the RANK measure, the visual breaks or returns incorrect results if that column isn't present in the visual's filter context.

The Question: RANKX allows me to mention only the partition and the fact table without manually babysitting the filter context for every row-level attribute I might add or remove.

Is there a way to write RANK so it behaves like RANKX? Specifically, can I define the RANK relation to be "whatever is currently in the visual" plus the fact table, without explicitly listing every dimension column? Or is RANK strictly tied to a fixed relation that must be updated every time the visual changes?

I am not looking for a solution through visual calculation.

Thank you in advance. 

[amitchandak]

@smpa01 , Usually, a column in Visual that is not used in Rank or Rankx is added as an additional partition in the calculation

Other than the below rank option, you can try visual calculation with the reset option, but that will not allow order by 

Rank = RANK(DENSE,ROWs,,,,,LOWESTPARENT)

Try like 

dax_RANK :=
IF(
NOT ISBLANK([fact_sum]),
IF(
ISINSCOPE(dim_name[name]),
RANK(
DENSE,
SUMMARIZE(ALLSELECTED('fact'), dim_period[period], dim_id[id], dim_name[name]),
ORDERBY([fact_sum], DESC, dim_id[id], ASC),
PARTITIONBY(dim_period[period])
),
RANK(
DENSE,
SUMMARIZE(ALLSELECTED('fact'), dim_period[period], dim_id[id]),
ORDERBY([fact_sum], DESC, dim_id[id], ASC),
PARTITIONBY(dim_period[period])
)
)
)

[smpa01]

Thanks @amitchandak . But problem remians the same. RANKX lets me get away with minimum maintenance with fact and partition. Byt RANK needs me to build a Relations table for the Relations parameter that needs all the rows that are present in the visual. When you add or remove rows to the visual, you need to go back to the measure and manually add them, which is not what I want. 

When I pass fact table to RANKX it doesn't care about the columns in your visual. It is a preferred pattern for me cause I don't want to keep track what visual gets added or removed.

Can RANK provide exact same facility ?

[FBergamaschi · Accepted solution]

Hi @smpa01,
I created the RANK version as follows

dax_RANK =

RANK (

    DENSE,

    ADDCOLUMNS(

    ALLSELECTED( 'fact' ),

    "@period", RELATED( dim_period[period] ),

    "@Mycol", [fact_sum]

    ),

    ORDERBY ( [@Mycol], DESC ),

    PARTITIONBY ( [@period] )

)

It works in my pbix both with a without the name in the visual

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI