RANKX based on a measure across two tables

[Anonymous]

Hi guys,

i need to create a rankx measure to get the top 10 values

i have two different tables:

There's a measure in each table:

table a:

totalA = DISTINCTCOUNT(tablea[EPPARNR])

table b: 

totalB =
CALCULATE(
DISTINCTCOUNT(tableb[EPPARNR]);
FILTER(tableB;
AND(tableb[DAYS_BETWEEN] >=0;
tableb[DAYS_BETWEEN] <= 30)))

then, i created a measure to get the percentage:

% RV = divide(totalB; totalA)

now, i need to get the top 10 result. It's more than 5mi rows each table, i tried to use the % measure in a bar chart but took too long to respond 😞

i also tried some measures like rankx + all + calculate, but didn't work.

Thanks a lot!

[v-xicai]

Hi  @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous]

Hi @v-xicai,

unfortunelly, it couldn't help.

Actually, that's exacly the result that i need, but with a better performance. When i tried "Top N", it took several minutes to work.

Is a big dataset and i need to use a measure in the data field. I think is the reason to a bad performance.

[v-xicai]

Hi @Anonymous ,

You may go to Filter Pane, choose option Top N.  At that point, you may just drag in the measure [% RV] you want the Top N to run.

Best Regards,

Amy 

Community Support Team _ Amy

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

[parry2k]

@Anonymous first this measure need change

table b: 

totalB =
CALCULATE(
DISTINCTCOUNT(tableb[EPPARNR]);

tableb[DAYS_BETWEEN] >=0;
tableb[DAYS_BETWEEN] <= 30)

read this for ranking

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡