Rankx multiple tables

[cjcj]

Hi, I have a table visualization pulling in data from multiple connected tables and a measure and I am seeing unexpected results when adding a rankx measure.

RANKX (
ALLSELECTED ('MeasureTableX'),
CALCULATE (
[Sales],
ALLEXCEPT(Table1, Table1[ID]),
ALLEXCEPT(Table2, Table2[Location]),
ALLEXCEPT(Table3, Table3[Product]))
,,DESC
)

any thoughts would be appreciated.

[v-shex-msft]

Hi @cjcj,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[bcdobbs]

Hard to diagnose without some dummy data/ideally an example pbix. However... straight away I would look to remove the ALLEXCEPT lines and instead use an ALL / VALUES approach. Have a read of https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ you tend to avoid a lot of pain that way,

[bcdobbs]

How are the tables related. Can you describe MeasureTableX? I suspect that might be causing the issue as you really want to rank over a table at the same grain as your visual.

[cjcj]

Hi, MeasureTableX is just a table of measures...so not really a table.  Not sure if rankx syntax would take a table of measures vs an actual table.