Circular dependency in a rankx

[StephenF]

I'm trying to get a column to rank spend against 2 categories eg, whats 1,2,3 in spend for say Motors or something.

I'm getting a wierd cicular dependency error.

I've attached a small pbix with the data table and my attempt at it if you can have a stab at it please.

Data/PBIX is behind this link here.

Other We transfer link if that doesnt work.

[v-yangliu-msft]

Hi  @StephenF ,

Here are the steps you can follow：

1. Create calculated column.

Rank 4 =
RANKX(FILTER(ALL(Spends),'Spends'[Major Category]=EARLIER('Spends'[Major Category])&&'Spends'[Parent Client]=EARLIER('Spends'[Parent Client])),[InvSpend],,DESC)

2. Result:

Best Regards,

Liu Yang

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

[StephenF]

This ranks across three categories. I wish to rank across two - category and client and be able to filter on year which would change the result dynamically.

I do not think this is possible with a calculated column as per your example as the results are static.

[StephenF]

@amitchandak Can you take a look, ive corrected the links address by adding a second.

[amitchandak]

@StephenF , it is showing the file is deleted , please share again

refer : https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

[StephenF]

Apologies, try again.