topic TOP N Visual with Parameter in DAX Commands and Tips

TOP N Visual with Parameter

kaintxu2 — Wed, 09 Oct 2024 16:08:07 GMT

Hi everyone,

The other day I got some help making it posible to choose between 2 meassures to apply to a visual as can be seen below (image 1). Sales in $ vs sales in quantity which was solver with "New Parameter" function.

My actual issue is that while it is working perfectly, I want to add a TOP N filter. Usually when you add the TOP N it ask you by which value you want to short it. In this case it would be quantity or $, but when I add the newly create parameter which changes based on the selection, it is not doing a TOP N as see later (Image 2).

On some other site I was sugested to create a new meassure with a switch to use in the TOP N filter field, but it is not working and the visual returns an error that says : Calculation error in measures 'Pedidos Lineas' [TOP N Measure]: Column [Top N Cantidad/Importe] is part of a composite key, but not al columns of the composite key are included in the expression or it's dependent expression.

Here you have my code:

TOP N Measure = SWITCH(SELECTEDVALUE('Top N Cantidad/Importe'[Top N Cantidad/Importe]),NAMEOF([Cantidad]),[Cantidad],NAMEOF([Importe Linea]),[Importe Linea],Blank())

Can someone tell my how can I do so that it does a TOP N which changes from quantity to $ when I chose the specific parameter. Adding the parameter to the filters on this visual wont work as seen below.

Many thanks,

Re: TOP N Visual with Parameter

Anonymous — Thu, 10 Oct 2024 02:23:16 GMT

Hi kaintxu2 ,

Based on the description, try using the following DAX formula.

Selected Measure = SWITCH( SELECTEDVALUE('Top N Parameter'[Parameter]), "Cantidad", [Cantidad], "Importe", [Importe Linea], BLANK() )

Then, using the following measure to filter the TOP N.

Rank Measure = RANKX( ALL('YourTableName'), [Selected Measure], , DESC )

If the provided expression doesn't work, can you provide simple example data?

Best Regards,

Wisdom Wu

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

Re: TOP N Visual with Parameter

kaintxu2 — Thu, 10 Oct 2024 07:31:01 GMT

Hi, thanks for the answer, I will try and provide some data. What is the best way to do so, just copy paste 2 examples from a table?

On the other hand, that Dax you mentioned on the second code, could you specify which is the table has to be the ranked one? If I chose the one with the original measures would not make sense right? As we are ranking grouped by Item type (Y Axis), and there is 2 different measures we want to use. I have tried usin the RANKX on the parameter table and it is not working

Thanks

Re: TOP N Visual with Parameter

Anonymous — Mon, 04 Nov 2024 06:57:12 GMT

Hi kaintxu2 ,

Creating the TOPN measure. View the following DAX formula and modify the table and column name.

Besides, you can also view the following blog to learn more information.

Dynamic TopN made easy with What If Parameter - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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