topic Re: Ranking on calculated measure in DAX Commands and Tips

Ranking on calculated measure

GrahamR99 — Mon, 27 Nov 2023 09:18:33 GMT

Hello

I have this formula;

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

Re: Ranking on calculated measure

123abc — Mon, 27 Nov 2023 10:42:58 GMT

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.

Re: Ranking on calculated measure

GrahamR99 — Mon, 27 Nov 2023 11:04:29 GMT

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

Re: Ranking on calculated measure

123abc — Mon, 27 Nov 2023 12:16:22 GMT

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.

Re: Ranking on calculated measure

GrahamR99 — Mon, 27 Nov 2023 14:16:06 GMT

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

Re: Ranking on calculated measure

GrahamR99 — Tue, 28 Nov 2023 11:46:00 GMT

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 Postcode	Sup Postcode	Distnace (km)	Rank
AAA	ABC	20	2
AAA	BCD	30	3
AAA	DEF	10	1
BBB	ABC	50	3
BBB	BCD	10	2
BBB	DEF	5	1

How do I write the rank formual for this?

Regards

GrahamR99

Re: Ranking on calculated measure

123abc — Tue, 28 Nov 2023 11:54:49 GMT

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:

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.
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.

Re: Ranking on calculated measure

123abc — Tue, 28 Nov 2023 11:57:48 GMT

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.

Re: Ranking on calculated measure

GrahamR99 — Tue, 28 Nov 2023 13:18:30 GMT

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

Re: Ranking on calculated measure

GrahamR99 — Tue, 28 Nov 2023 13:42:57 GMT

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 Postcode	Sup Postcode	Distance (km)	Rank
AAA	ABC	20	2
BBB	ABC	30	3
CCC	ABC	10	1
DDD	ABC	50	4
AAA	ZXY	50	3
BBB	ZXY	60	4
CCC	ZXY	20	2
DDD	ZXY	10	1

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

Re: Ranking on calculated measure

Dangar332 — Tue, 28 Nov 2023 13:58:15 GMT

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]) )

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]) )

Re: Ranking on calculated measure

GrahamR99 — Tue, 28 Nov 2023 14:15:22 GMT

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

Re: Ranking on calculated measure

Dangar332 — Tue, 28 Nov 2023 14:43:09 GMT

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]) )

Re: Ranking on calculated measure

GrahamR99 — Tue, 28 Nov 2023 15:09:29 GMT

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

Re: Ranking on calculated measure

Dangar332 — Tue, 28 Nov 2023 15:20:42 GMT

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]) )