Forum Discussion

BowPen's avatar
BowPen
Frequent Visitor
3 years ago

ALLSELECTED

Im using RANKX(ALLSELECTED because I want to rank base on what is shown on my visual. Sample, I filter on a visual to only get the top 15 and I want those ranked from 1 to 12. Using ALLSELECTED work but it takes a long time to load the result since its checking each row of the entire table. Is there an alternative for this? I can only imagine the impact when we reach a 500k rows or more.

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    What kind of visual is it?  Use Performance Analyzer to check the DAX query that feeds your visual. You can use that as the basis for your ranking measure.

    • BowPen's avatar
      BowPen
      Frequent Visitor
      3 years ago

      // DAX Query
      DEFINE
      VAR __DS0FilterTable =
      FILTER(
      KEEPFILTERS(VALUES('plantedtrial'[plant_quantity])),
      'plantedtrial'[plant_quantity] > 0
      )

      VAR __SQDS0Core =
      SUMMARIZECOLUMNS(
      'variety'[variety_name],
      __DS0FilterTable,
      "yieldplantquantity", 'yield_measures'[yieldplantquantity]
      )

      VAR __SQDS0BodyLimited =
      TOPN(15, __SQDS0Core, [yieldplantquantity], 0)

      VAR __DS0Core =
      SUMMARIZECOLUMNS(
      'variety'[variety_name],
      __DS0FilterTable,
      __SQDS0BodyLimited,
      "sortr3", 'rankround_measures'[sortr3],
      "eliminatedrank", 'rankround_measures'[eliminatedrank],
      "rankround1", 'rankround_measures'[rankround1],
      "rankround2", 'rankround_measures'[rankround2],
      "rankround3", 'rankround_measures'[rankround3]
      )

      VAR __DS0PrimaryWindowed =
      TOPN(1001, __DS0Core, [sortr3], 0, 'variety'[variety_name], 1)

      EVALUATE
      __DS0PrimaryWindowed

      ORDER BY
      [sortr3] DESC, 'variety'[variety_name]

      here's the performance review and the visual is just the stacked bar chart

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        now take this query into DAX Studio and run it there after enabling Query Plan.  Check for excessive number of records.  See if you can isolate the performance of your individual rankround measures.