RankX with Filters

Question

Hi,&nbsp;I have a RankX formula that almost works.I have a visual with a table that has each model we have but it needs to filter based on the account picked or the status of model ect.This visual has multiple description fields for example, the intro date of the model the msrp ect and then measures with sales.&nbsp;The current Rankx formula i'm using is:Calculate(Rankx(All("Model column"),"Sales Measure"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"))&nbsp;It almost gives me the result i am looking for. The table "model" is coming from is the Master product table and it has about 10,000 distinct models. So this rank formula says my lowest rank is 10,000, but i want it to be say 250 (the amount of models showing in the visual after the filters are applied)How can i adjust the formula or it is possibly my relationships and i shouldn't be using the model column from the master product table?&nbsp;Thank you!

amitchandak · Answer

For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

v-lili6-msft · Answer

hi&nbsp;&nbsp;mrpowrbihelp&nbsp;
You need to use ALLSELECTED instead of ALL in the rank measure
http://tinylizard.com/all-vs-allselected/
&nbsp;
If you still have the problem, please share the sample pbix file and your expected output.
&nbsp;
Regards,
Lin

owenauger · Answer

Hi&nbsp;mrpowrbihelp&nbsp;&nbsp;It would be best to see the tables/relationships, along with which columns you are using in the visual.Could you possibly post a pbix with santised data?&nbsp;But based on your description so far, here is a possible measure structure, which I could be more confident in with some more details on the model.&nbsp;=
VAR ModelsForRanking =
    CALCULATETABLE (
        SUMMARIZE ( SalesTable, ModelTable[Model Column] ),
        ALLSELECTED ()
    )
RETURN
    RANKX (
        ModelsForRanking,
        CALCULATE ( "Sales Measure", ALLEXCEPT ( ModelTable, ModelTable[Model Column] ) )
    )&nbsp;Kind regards,Owen

Forum Discussion

RankX with Filters

3 Replies

Related content

RANKX with filter

Rankx with filter

RankX

RANKX Filter

Advanced Ranking (Rankx)