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

[Anonymous]

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.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Assume you have tables

 

Create measures

Measure = SUM(Table1[percent])

Measure 2 = RANKX(ALL(Table2),[Measure],,DESC,Dense)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.