Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

Do a TopN on a field parameter measure

I have this field measure: prmMeasure = {     ("Tot.LC", NAMEOF('Metrics'[Tot.LC]), 0),     ("Tot.GC", NAMEOF('Metrics'[Tot.GC]), 1),     ("Quantity", NAMEOF('Metrics'[Quantity]), 2) }   So t...
Need Help
  • rpinxt's avatar
    rpinxt
    2 years ago

    Ok I have been able to fix it.

    Using a ranking based on the variable field parameters I was able to create a top 10:

    So the second column is variable and takes the measure that is chosen in the slicer.

    To make this work I had to add a copy/dummy field in my field parameter:

    If you use the first column in the parameter you get errors in coming formulas.

     

    Made the rank like :

    So 1 var to determine what the choise was.

    Then 3 other vars for every single option of choise.

     

    With a switch you can then contol the rankx on which selected value from the field parameter options.

    Then you have a field that ranks the selected measure from 1 to xxxx.

     

    Only thing you then need to do is to put a filter on where you only show 10 or less :

    Then I think the only option we have is for the visual to switch off word wrap on the headers and on the values.

    After yoi can manually make the column Rank_Measure very small so it will not show.

     

    ps: if somebody knows a better way to hide a column in a table visual please let me know!

     

     

     

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
2 years ago

Well I tried again with the logic in that video.

But again rank shows 1 eveywhere.:

So I used the order of the parameter field tell which the measure should be that needs to be sorted.

And the rankx in 3 cases is on the same table (PF and field Prod.Fam.)

 

Guessing the allselected is not working because it looks at only 1 row and not all rows in the selection.

Therefore everywhere the rank is 1.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rpinxt ,

    I create a table as you mentioned.

    Then I create a calculated column and here is the DAX code.

    Column = RANKX(ALL('Table'),'Table'[Sales],,ASC,Dense)

    Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

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

    • rpinxt's avatar
      rpinxt
      Icon for Solution Sage rankSolution Sage
      2 years ago

      Thanks Anonymous but what I am missing here is the part where ranking is based on a measure that will be changed by a field parameter.

       

      That is where the difficulty is.

      Lets say to make it easier you have a Sales Measure and you have a Cost measure.

      You make 1 field parameter that switches between Sales and Cost.

       

      Now you cannot make a ranking as calculated column because 1 time the user selects Sales and the other time he selects Cost.