Mastering the RANKX Function in Power BI – From Common Errors to Dynamic Rankings

Power BI offers powerful DAX functions, and RANKX is one of the most useful when you want to assign ranks based on measures like sales, profit, or quantity. But if you're new to it, it might not work as expected at first. In this blog, I'll walk you through a real-world scenario where I explored RANKX, faced common issues, and how I solved them.

Syntax of RANKX

RANKX(
    table,
    expression,
    [value],
    [order],
    [ties]
)
Parameter Description
table The list (table or column) over which ranking is done.
expression The expression to evaluate for each row (usually a measure like [total sale]).
Value(optional) A value to rank (rarely used; DAX infers automatically).
order(optional) ASC (ascending) or desc (descending). Default is asc
ties(optional) How to handle ties: skip, Dense or leave blank (default is Skip).

our goal is we want to calculate a rank for products based on total sales using DAX.

Sample data:


Create a total sale measure:
Total Sale = Sum(Sale[Sales])

First Attempt – RANKX on Sales Table

Product Rank =
RANKX(
    Sale,
    [Total Sale],
    ,
    DESC
)

Problem: All rows returned blank or all ranks were 1.

Root Cause

I was ranking on Sale, which was the fact table. In Power BI, when you rank over a column from a fact table, DAX often doesn't get the full list of distinct values due to row context - especially when writing a measure.

Fix - Use a Separate Dimension Table

I created a separate Product table with distinct product names:

Product = DISTINCT(Sale[Product])
create a relationship as shown in below image:

In table values add product column from Product table that we created using distinct DAX function.
Then we are getting proper rank as shown below.





Comments

Post a Comment

Popular posts from this blog

Step-by-Step Guide: Power Automate Custom Connector Using Graph API from Azure App Service

Image
Step 1: Open Power Automate, navigate to the Custom Connectors tab, and select Create from blank as shown in the image below. custom connector(click image for better resolution) Step 2: In the General tab, upload an icon (less than 1MB), add a description, select the correct scheme (in my case, HTTPS ), and provide the host name. For example, if your API URL is https://graph.microsoft.com/v1.0/users/{email} , the host name will be the highlighted part — graph.microsoft.com . Step 3: Go to the Security tab. Here, you need to select the authentication method. Choose OAuth 2.0 as the authentication type, as shown in the image below. Step 4: Select the Identity Provider as Azure Active Directory . Check the box Enable Service Principal Support . This option allows you to create a connection using a Client ID and Client Secret instead of using the user's own authentication. Next, provide the Client ID , Client Secret , Tenant ID , and other required details as shown in t...
Read more

Calling Microsoft Graph API from Power Automate Using Azure App Services – Step-by-Step Guide

Image
Step 1: You should have an Azure subscription, or if you have access to the Microsoft Entra Admin Center, you can create an app from there. This app can then be used in Power Automate to connect to Microsoft Graph API. Step 2: Click on "New registration" , provide a name for your app, assign the required API permissions (such as Microsoft Graph permissions), and then click "Register" to complete the app registration process. Step 3: After registration, you'll be redirected to the app's overview page. From there, navigate to the API permissions tab and click "Add a permission" to assign the necessary Microsoft Graph API permissions. Step 4: Select Microsoft Graph as the API, and based on your requirements, choose the appropriate permissions. In my case, I selected Application permissions , which do not require user sign-in and are suitable for background services or automation. Step 5: Select the required Microsoft Graph API permissions. Yo...
Read more

Step-by-Step: Give Unique Permissions to OneDrive Files Using Power Automate and Graph API (No Premium License Needed)

Image
We are uploading files through Power Apps using the Attachment control. In the backend, a Power Automate flow uploads these files to OneDrive and assigns unique permissions . Since Power Automate does not have a built-in connector to directly assign unique permissions in OneDrive, we are handling this through the Microsoft Graph API (without using a custom connector). Step 1: Create a Blank Power Apps Application Open Power Apps Studio. Select New app > Start with a page design as shown below. Step 2 :  select Blank Canvas  Step 3: Add an Attachment Control In Power Apps, the Attachment control is not available directly from the standard controls list. To get the Attachment control: Add an Edit Form to your app. Connect the form to a SharePoint list . Once connected, the form will automatically include the Attachments field. You can then use this Attachment control to upload files. Step 4: Keep Only the Attachment Control Since we only need th...
Read more