RANKX return blank when rank is tied

[Paulien_]

Hi,

I am trying to build the follow table in which the rank is calculated using the RANKX function. 

Rank	Product	# views
1	Bike	300
2	Car	200
	Bus	200
3	Train	40
4	Airplane	3

However, my rank formula returns the table below. If the ranks are the same, the rank is duplicated. I was wondering, instead of showing a duplicate rank, is it possible to show a "blank" of the same rank occurs multiple times? 

Rank	Product	# views
1	Bike	300
2	Car	200
2	Bus	200
3	Train	40
4	Airplane	3

Current formulas I use:

# views = count(viewid)

Rank = RANKX(ALLSELECTED('Product'[Product]),[# views],,DESC,Dense) 

Thank you!

[Anonymous · Accepted solution]

Hi @Paulien_ ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Transform the data in Power Query Editor as below screenshot: add count column and index column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMTlXSUUoyMFSK1UHiGqFyjVG5JqhcUzDXObEIyEuGmhRSlJiZB+SXQPmOmUUFOYl5IA2JUCGYBiMUHtSq0mKQ0aUwd8G4RqhcYzS7gNKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, viewid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"viewid", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"viewid", each _, type table [Product=nullable text, viewid=nullable text]}}),
    #"Expanded viewid" = Table.ExpandTableColumn(#"Grouped Rows", "viewid", {"viewid"}, {"viewid"}),
    #"Sorted Rows" = Table.Sort(#"Expanded viewid",{{"Count", Order.Descending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows", {"Count", "Product"}, {{"Details", each _, type table [Product=nullable text, Count=number, viewid=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows1", "Index", 1, 1, Int64.Type),
    #"Expanded Details" = Table.ExpandTableColumn(#"Added Index", "Details", {"viewid"}, {"viewid"})
in
    #"Expanded Details"

2. Create a calculated column as below

Rank = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Product'[Product] ),
        FILTER ( 'Product', 'Product'[Count] = EARLIER ( 'Product'[Count] ) )
    )
VAR _minindex =
    CALCULATE (
        MIN ( 'Product'[Index] ),
        FILTER ( 'Product', 'Product'[Count] = EARLIER ( 'Product'[Count] ) )
    )
VAR _rank =
    RANKX ( 'Product', 'Product'[Count],, DESC, DENSE )
RETURN
    IF ( 'Product'[Index] <> _minindex, BLANK (), _rank )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Paulien_]

Thank you for your suggestion, unfortunately the mentioned tie breaker is not what I need.

[amitchandak]

@Paulien_ , I would suggest to use tie breaker instead of making it blank

Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/