Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
GrahamR99
Resolver I
Resolver I

Ranking on calculated measure

‎11-27-2023 01:18 AM
Hello
I have this formula;
Distance (Rank) = RANKX(FILTER('Legacy Events Planning 2024','Legacy Events Planning 2024'[Supporter_Postcode]=EARLIER('Legacy Events Planning 2024'[Supporter_Postcode])),'Legacy Events Planning 2024'[Distance (km)],,ASC,Dense)
I get this error;
A circular dependency was detected.
Distance (km) is a calculated field.  Which I guess is the reason I get this error.
How do I rank the data and get around this error?
 
Thank you for reading.
 
Regards
 
GrahamR99
Labels:
Message 1 of 21
1,455 Views
0
2 ACCEPTED SOLUTIONS
Dangar332
Super User
Super User
In response to GrahamR99

‎11-28-2023 05:52 AM

hi, @GrahamR99 

@GrahamR99 

 

try to make new column 

 

Column = 
RANK(
     DENSE,
     ALLSELECTED(
         'Table (2)'[Sup Postcode],
         'Table (2)'[Distance (km)]
    ),
    ORDERBY('Table (2)'[Distance (km)],ASC)
    ,,PARTITIONBY('Table (2)'[Sup Postcode])
)

 

 

Dangar332_0-1701179548706.png

 

if you want to measure then try below code

Measure 2 = 
var a = ALL('Table (2)'[Distance (km)],'Table (2)'[Sup Postcode])
return
RANK(
    DENSE,
    a,
    ORDERBY(
        'Table (2)'[Distance (km)],
        ASC
    ),,
    PARTITIONBY('Table (2)'[Sup Postcode])
    
)

 

Dangar332_1-1701179883881.png

 

 

View solution in original post

Message 10 of 21
1,368 Views
0
GrahamR99
Resolver I
Resolver I
In response to Dangar332

‎12-14-2023 01:51 AM

Hello @Dangar332 

Thank you for the formula, it worked when I change the data.

 

When I first setup the Power BI report, I used Power BI mesure to calculate the distance between two locations.  When I recreated the data in SQL Server and used it's ablity to calulate the distance, the data was a column and I could use your formula and also slice the distance column in my Power BI report which I could not do when it was a mesure.

 

The SQL Script I used was;

DistanceInMiles = GEOGRAPHY::Point([Lat], [Long], 4326).STDistance(GEOGRAPHY::Point([latitude], [longitude], 4326)) / 1609.344

 

Your formula I used was.

 

Column = 
RANK(
     DENSE,
     ALLSELECTED(
         'Table (2)'[Sup Postcode],
         'Table (2)'[Distance (km)]
    ),
    ORDERBY('Table (2)'[Distance (km)],ASC)
    ,,PARTITIONBY('Table (2)'[Sup Postcode])
)

 

Thank you for your help.

 

Regards

 

GrahamR99

 

View solution in original post

Message 17 of 21
1,121 Views
0
20 REPLIES 20
123abc
Super User
Super User

‎11-27-2023 02:42 AM

You can try this modified Dax funciton:

Distance (Rank) =
RANKX(
FILTER(
'Legacy Events Planning 2024',
'Legacy Events Planning 2024'[Supporter_Postcode] = EARLIER('Legacy Events Planning 2024'[Supporter_Postcode])
),
CALCULATE('Legacy Events Planning 2024'[Distance (km)]),
,
ASC,
Dense
)

 

In this modified formula, I replaced 'Legacy Events Planning 2024'[Distance (km)] with CALCULATE('Legacy Events Planning 2024'[Distance (km)]). This can help break the circular dependency, as CALCULATE allows you to create a context transition and evaluate the measure in a different context.

Try this modification and see if it resolves the circular dependency issue. If you encounter any further issues or if you have additional requirements, feel free to provide more details.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Message 2 of 21
1,420 Views
0
GrahamR99
Resolver I
Resolver I
In response to 123abc

‎11-27-2023 03:04 AM

Hello @123abc 

I get a different error now which is;

Earlier/Earliest refers to an earlier context which doesn't exist.

 

Do you know how to resolve?

 

Regards

 

GrahamR99

Message 3 of 21
1,413 Views
0
123abc
Super User
Super User
In response to GrahamR99

‎11-27-2023 04:16 AM

Plz use this modified DAX:

Distance (Rank) =
RANKX(
FILTER(
'Legacy Events Planning 2024',
'Legacy Events Planning 2024'[Supporter_Postcode] = EARLIER('Legacy Events Planning 2024'[Supporter_Postcode])
),
CALCULATE(
SUM('Legacy Events Planning 2024'[Distance (km)]),
ALLEXCEPT('Legacy Events Planning 2024', 'Legacy Events Planning 2024'[Supporter_Postcode])
),
,
ASC,
Dense
)

 

if not work please sahre Pbxi file or excel file with sample data.

Message 4 of 21
1,412 Views
0
GrahamR99
Resolver I
Resolver I
In response to 123abc

‎11-27-2023 06:16 AM

Hello @123abc 

The last formula didn't work,.

 

I can't upload a file on this forum and my organisation will not let me share the file to anyone out side the organisation on Onedrive.

 

You are going to have to explain how I get the file to you?

 

Regards

 

GrahamR99

Message 5 of 21
1,404 Views
0
123abc
Super User
Super User
In response to GrahamR99

‎11-28-2023 03:57 AM

If you encounter a specific error or face challenges with a particular DAX formula, feel free to share the relevant portions of the code or describe the issue in detail. I'll do my best to provide guidance based on the information you provide within the text format of this conversation.

If you have specific DAX expressions or parts of your Power BI model that you'd like assistance with, please share the relevant details, and I'll try to help you troubleshoot the problem.

Message 21 of 21
1,383 Views
0
GrahamR99
Resolver I
Resolver I
In response to GrahamR99

‎11-28-2023 03:46 AM

Hello @123abc 

Is there another way you can help me?

 

I have three columns,

 

Site Postcode,

Sup Postcode,

Distance (km)

 

I have mulple sites, and a sup postcode for each site, then the distance (km) is calculated.

 

Site PostcodeSup PostcodeDistnace (km)

Rank

 

AAAABC20

2

AAABCD30

3

AAADEF10

1

BBBABC50

3

BBBBCD10

2

BBBDEF5

1

 

How do I write the rank formual for this?

 

Regards

 

GrahamR99

Message 6 of 21
1,385 Views
0
123abc
Super User
Super User
In response to GrahamR99

‎11-28-2023 03:54 AM

You can try this DAX:

Rank =
RANKX (
FILTER (
YourTable,
[Site Postcode] = EARLIER ( [Site Postcode] )
),
[Distance (km)],
,
ASC,
Dense
)

 

This formula uses the RANKX function to calculate the rank based on the 'Distance (km)' column for each 'Site Postcode' group. The EARLIER function is used to refer to the current row's 'Site Postcode' when filtering.

Here's a step-by-step explanation of the formula:

  1. FILTER(YourTable, [Site Postcode] = EARLIER([Site Postcode])): This part of the formula filters the table to include only rows where the 'Site Postcode' is equal to the current row's 'Site Postcode'. This ensures that the ranking is done separately for each site.

  2. RANKX(..., [Distance (km)], ..., ASC, Dense): This part of the formula calculates the rank based on the 'Distance (km)' column in ascending order (ASC) and using dense ranking (Dense).

You can add this DAX formula to your Power BI table to create the 'Rank' column. Keep in mind that you need to replace 'YourTable' with the actual name of your table.

Message 7 of 21
1,383 Views
0
GrahamR99
Resolver I
Resolver I
In response to 123abc

‎11-28-2023 05:18 AM

Hello @123abc 

Sorry it doesn't work, I get this error message.

Earlier/earliest refers to an row context which doesn't exist.

 

Why is this happing?

 

Regards

 

GrahamR99

Message 8 of 21
1,372 Views
0
GrahamR99
Resolver I
Resolver I
In response to GrahamR99

‎11-28-2023 05:42 AM

Hello @123abc 

Sorry I got the table wrong.

 

What I have is mulitple sites, and a set amount of supporters,  I have joined every site to every supporter, then calculated how far they are from each other.

 

Site PostcodeSup PostcodeDistance (km)Rank
AAAABC202
BBBABC303
CCCABC101
DDDABC504
AAAZXY503
BBBZXY604
CCCZXY202
DDDZXY101
    

 

How do I get this ranking, and the data is not sorted, so these are all mixed up.

 

How do I get the ranking?

 

Regards

 

GrahamR99

Message 9 of 21
1,370 Views
0
123abc
Super User
Super User
In response to GrahamR99

‎12-01-2023 02:31 AM

please share pbix file ... further i share with you my ranking issues and their solution hope your issue will be resolved.

 

OR follow this link:

(2) Rankx, Ranking by state and by city (multiple leve... - Microsoft Fabric Community

OR follow this link;

Solved: Re: RankX at Category level for Category/SubCatego... - Microsoft Fabric Community

OR follow this link:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/RANKX-for-Main-Category-then-for-fur...

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Message 18 of 21
1,289 Views
0
GrahamR99
Resolver I
Resolver I
In response to 123abc

‎12-01-2023 02:45 AM

Hi @123abc 

How do I share the Power BI file with you?

 

Regards

 

GrahamR99

Message 19 of 21
1,285 Views
0
123abc
Super User
Super User
In response to GrahamR99

‎12-01-2023 03:55 AM

1: Save your data on googel one drive.

2: Set your save data security to visiulize for every one.

3: Share link of your data here. 

Message 20 of 21
1,274 Views
0
Dangar332
Super User
Super User
In response to GrahamR99

‎11-28-2023 05:52 AM

hi, @GrahamR99 

@GrahamR99 

 

try to make new column 

 

Column = 
RANK(
     DENSE,
     ALLSELECTED(
         'Table (2)'[Sup Postcode],
         'Table (2)'[Distance (km)]
    ),
    ORDERBY('Table (2)'[Distance (km)],ASC)
    ,,PARTITIONBY('Table (2)'[Sup Postcode])
)

 

 

Dangar332_0-1701179548706.png

 

if you want to measure then try below code

Measure 2 = 
var a = ALL('Table (2)'[Distance (km)],'Table (2)'[Sup Postcode])
return
RANK(
    DENSE,
    a,
    ORDERBY(
        'Table (2)'[Distance (km)],
        ASC
    ),,
    PARTITIONBY('Table (2)'[Sup Postcode])
    
)

 

Dangar332_1-1701179883881.png

 

 

Message 10 of 21
1,369 Views
0
GrahamR99
Resolver I
Resolver I
In response to Dangar332

‎12-14-2023 01:51 AM

Hello @Dangar332 

Thank you for the formula, it worked when I change the data.

 

When I first setup the Power BI report, I used Power BI mesure to calculate the distance between two locations.  When I recreated the data in SQL Server and used it's ablity to calulate the distance, the data was a column and I could use your formula and also slice the distance column in my Power BI report which I could not do when it was a mesure.

 

The SQL Script I used was;

DistanceInMiles = GEOGRAPHY::Point([Lat], [Long], 4326).STDistance(GEOGRAPHY::Point([latitude], [longitude], 4326)) / 1609.344

 

Your formula I used was.

 

Column = 
RANK(
     DENSE,
     ALLSELECTED(
         'Table (2)'[Sup Postcode],
         'Table (2)'[Distance (km)]
    ),
    ORDERBY('Table (2)'[Distance (km)],ASC)
    ,,PARTITIONBY('Table (2)'[Sup Postcode])
)

 

Thank you for your help.

 

Regards

 

GrahamR99

 

Message 17 of 21
1,122 Views
0
GrahamR99
Resolver I
Resolver I
In response to Dangar332

‎11-28-2023 06:15 AM

Hello @Dangar332 

The Distance (km) is a measure and with both of your formulas it won't let me select it in parts of the formulas.

 

Is there anything else I can try?

 

Regards

 

GrahamR99

Message 11 of 21
1,362 Views
0
Dangar332
Super User
Super User
In response to GrahamR99

‎11-28-2023 06:43 AM

hi, @GrahamR99 

update code

Measure 2 = 
var a = ADDCOLUMNS(ALLSELECTED('Table (2)'[Site Postcode],'Table (2)'[Sup Postcode]),"@amt",CALCULATE([distance(km)]))
return
RANK(
    DENSE,
    a,
    ORDERBY(
        [@amt],asc
        ,'Table (2)'[Site Postcode],DESC
    ),,
    PARTITIONBY('Table (2)'[Sup Postcode])
    
)

 

Dangar332_0-1701182570222.png

 

Message 12 of 21
1,357 Views
0
GrahamR99
Resolver I
Resolver I
In response to Dangar332

‎11-28-2023 07:09 AM

Hello @Dangar332 

That formula works, as in it calculates, but all the rankings are 7, which is how many sites I have with different sup postcodes.

 

Is there anything else we can try?

 

The solution maybe very simple, I don't know how to use the rankx formula and only started with one I found on google.

 

Regards

 

Graham Rock

Message 13 of 21
1,354 Views
0
Dangar332
Super User
Super User
In response to GrahamR99

‎11-28-2023 07:20 AM

HI, @GrahamR99 

it work without calculate also

Measure 2 = 
var a = ADDCOLUMNS(ALLSELECTED('Table (2)'[Site Postcode],'Table (2)'[Sup Postcode]),"@amt",[distance(km)])
return
RANK(
    DENSE,
    a,
    ORDERBY(
        [@amt],asc
        ,'Table (2)'[Site Postcode],DESC
    ),,
    PARTITIONBY('Table (2)'[Sup Postcode])
    
)

 

Message 14 of 21
1,350 Views
0
GrahamR99
Resolver I
Resolver I
In response to Dangar332

‎11-28-2023 07:30 AM

Hi @Dangar332 

That is 7 too for all rows. No ranking.

 

Regards

 

GrahamR99

Message 15 of 21
1,337 Views
0
GrahamR99
Resolver I
Resolver I
In response to GrahamR99

‎12-01-2023 01:59 AM

Hello @Dangar332 

Any idears why I'm always getting 7 on each row?

 

Is there anything else we can try?

 

Regards

 

GrahamR99

Message 16 of 21
1,294 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All