How to from slicer value find other table related ID value?

[wangjuan303]

I have two tables no relationship,  one table will create slicer for user to pick, another table will create a report to show detailed data,  I need create a measure base on the Slicer value to filter the detail table, I create Measure for sales, I also need create measure filter for ID column, How to create measure for ID column,  Thank you for help.

I know it can filter from flag measure or Sales measure, But in my real report, if I setting filter for ID column, will show over 1000000 row error, because detail table has big volume data, so I need create measure to get ID column 

[v-kkf-msft · Accepted solution]

Hi @wangjuan303 ,

Please try the following measure:

Rank = 
IF (
    [Flag] <> BLANK (),
    RANKX ( ALLSELECTED ( Detail[ID] ), [Sales Measure],, desc )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@wangjuan303 , code seems fine. if this direct query and you are getting error then limit ID column using rank or TOPn (filter level topN)

Also refer https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[wangjuan303]

@amitchandak Thank you for your suggestion, yes, my report use direct query,  I try use rankx, it can show, but it run about 3 mins, Can you give me any suggestion? Thank you  

Rank = RANKX(ALL(Detail[ID]),[Sale Measere],,desc)   this run 3 mins

or I need filter Detail[ID] first, then rank it, Can you suggestion some code, Thank you very much