RANKX measure is ranking data from a relationship table that is not in my dataset

[NNaj]

Hi All,

I have written this measure to rank my ID entries based on hit %.

Current Quarter Ranking = RANKX(DEXTR, [Hit %])

In another table, I have ID's and company names to help identify which companies are being referred to. In this table there are more companies and ID's than in dataset. This is where I believe my problem is arising. (e.g. ID 1 is company A, ID 2 is company B,..)

When I add the measure above to my table, it adds rows to my table for the other companies not referred to in my dataset. How can I avoid this?

The image below shows how the ranking is: 1) incorrect order (I want the highest % to be ranked higher). 2) Ranking extends for the CP_ID beyond those in the data set because of the above mentioned relationship table where I extract the company name from the ID.

Any help will be appreciated.

Thank you in advance.

[Stemar_Aubert]

You can check out how to use CALCULATE here : https://www.absentdata.com/power-bi/how-to-use-calculate-in-power-bi/ 

What I like to do with RANKX is to nest it within a CALCULATE function, so I can apply filters to it easily.

For exemple, in your model you can try 

CQR = Calculate(rankx(allselected([DEXTR]),[HIT %]))

Which will only take what is currently selected on your page.

[NNaj]

HI @Stemar_Aubert 

Thanks for the help,

Unfortunately im still having the same issues.

[v-diye-msft]

Hi @NNaj 

Take a try of this measure: 

Current Quarter Ranking = RANKX( All(DEXTR[CP_ID]), [Hit%],,,Dense)

[NNaj]

Hi @v-diye-msft ,

Thanks for your message. I tried this but im still having the same problem.

[Washivale · Accepted solution]

Hi @NNaj ,

Try this

Calc_Rank = Rankx( All(Table[CP_ID]), Calculate([Hit%]))

let me know if it works.

Thanks,

Washivale

[NNaj]

Hi @Washivale 

I am still having the same problem where the CP_IDs are shown from the table there is a relationship with. Also, the ranking displays all 1's.

[Washivale]

Hi @NNaj ,

Which table column are you using in "ALL(Table[CPI_ID])" part of the function. Is it your current table or the second table where you have all ID's. Try creating two measures using both the options and let me know if outcome of both is same.

thank you,

Washivale