Do a TopN on a field parameter measure

[rpinxt]

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 the result is one of these 3 measures.

I use this field parameter in this simple table:

Works perfectly. When I choose for instance Tot.GC the 2nd column will change from Tot.LC to Tot.GC and with corresponding amounts by line.

However it shows me all lines and we are actually only interested in the Top 10 lines.

Normally you would put a TopN filter on it. But that does not seem to work with field parameters.

Does anybody know how I can get to show only a top 10 for my selected field parameters?

[rpinxt · Accepted solution]

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]

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]

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]

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.

[rpinxt]

Thanks @Sergii24 , I actually saw this video but I don't think this will help me.

The parameters here are fields in a table and then get (the same) measure for the choises.

My field parameter is based on measures which are selectable for every tabe the same table field.

I could not make the rankx working for this.

Gave me rank 1 on every line and also added blanks lines.

[Sergii24]

Hello @rpinxt, take a look at a similar thread (link) and this video (link).

Good luck!