How to rank a measure based on two column from different two table

[Anonymous]

Hello,

I am trying to do rank a measure based on two column. one column is from table1 and other is from table2. And there is one to many relation between the two table.  

I am using below dax- 

Ranking of employee per center per plan = CALCULATE([# of employee],FILTER(VALUES(Table1[center]),FILTER(VALUES(Table2[plan]),IF(RANKX(ALL(Table1[center],Table2[plan),[# of employee],,DESC,Dense)<=5,[# of employee],BLANK()))))

And getting the below error-->

Can anyone help me with the dax?

[V-lianl-msft]

See if this thread can help you:

https://community.powerbi.com/t5/Desktop/RANKX-measure-across-two-tables-not-working/td-p/468549 

If the problem still exists, please remove the sensitive data and provide a sample pbix and the results you expect, so as to make your scenario clearer.

[amitchandak]

@Anonymous , Try like examples

rank = RANKX(Summarize(ALL(Table),Table1[center],Table2[plan]), [# of employee], , DESC)

rank = RANKX(Summarize(allselected(Table),Table1[center],Table2[plan]), [# of employee], , DESC)

[Anonymous]

@amitchandak 

after allselected, which table needs to be used?

[Anonymous]

Hi @amitchandak 
I have used the formula that you have suggested.
I have used center from one table & plan from another table.

But getting the below error-

Can you look into it?

[amitchandak]

@Anonymous , Please share the formula. Check Summarize, should have table that is getting closed properly 

example

summarize(allselected(Table) , Table[Col], Table2[Col])

[Anonymous]

Hi @amitchandak ,
Basically, 

I have below chart

In this gragh I have taken count of emp id and group by center & plan.

Center is from one table and Plan is from another table. Count of employee id is from the table from where i have taken Plan.
So my requirement is only to showTop 5 employee count per Center per plan.

I have mapped center table with plan table with employee id.