USE PARAMETER VALUE FOR A COLUMN VARIABLE

[Syndicate_Admin]

Good evening, I have the following variable to create a new column in which according to the ranking number determined by the value of the Total field per family it is determined, according to the var Quantity, if said family is in the Top or in Altres. This variable works correctly but I would like to change dynamically the value 5 of the variable quantity that is now fixed by using a parameter or something similar. Not being a measure I can't get it to take the value dynamically:

ranking new columna =

where numeroranking = RANKX(ALLSELECTED('TABLE 11 SUMMARISED without week'),'TABLE 11 SUMMARISED without week'[TOTAL BY FAMILY],,DESC,Dense)

where quantity= 5 ------power changing the value of this variable (quantity) dynamically.

return

if(numeroranking<=quantity,"top","others")

Many gràcias for their time and help,

Greetings from Spain.

[Syndicate_Admin]

Hello MFelix, thank you very much for your reply. In the Calculated Column, the call to the parameter value still does not work:

SELECTEDVALUE ( Parameter[Parameter] )

the problem could be, as I have read, that the functions used internally by HASONEVALUE are incompatible to create table columns with parameter fields.

If it were a measure it would work.

Thank you very much for your time.

Greetings,

[MFelix]

Hi,

What type of parameter are you using?

[Syndicate_Admin]

Good evening, thank you for your interest and time.

I use a numerical range created from a new Parameter.

Then in the calculated column, in the variable I try to call the value with the expression:

SELECTEDVALUE ( Parameter[Parameter] )

But it does not take the courage to make the subsequent comparison:

where numeroranking = RANKX(ALLSELECTED('TABLE 11 SUMMARISED without week'),'TABLE 11 SUMMARISED without week'[TOTAL BY FAMILY],,DESC,Dense)

where quantity= SELECTEDVALUE ( Parameter[Parameter] )

return

if(numeroranking<=quantity,"top","others")

If a number is entered instead of SELECTEDVALUE (Parameter[Parameter]), the calculated column works correctly.

A very cordial greeting,

Xavi.

[MFelix]

Hi @Xavier2609 ,

When using this type of values you need to have a slicer with a single selection on your report, it's created automatically if you have the option turn on at the moment of the creation of the parameter. Do you have a slicer on your report?

[Syndicate_Admin]

Good afternoon and as always, thank you in advance for your help.

The parameter is selected from a slicer with a single selection.

It seems very simple:

ranking new columna =

where numeroranking = RANKX(ALLSELECTED('TABLE 11 SUMMARISED without week'),'TABLE 11 SUMMARISED without week'[TOTAL BY FAMILY],,DESC,Dense)

where quantity= 5

return

if(numeroranking<=quantity,"top five","others")

replace the quantity number 5 with a dynamic value selected in a slicer originating in a parameter. But being a calculated column I can't get it in any way that works. I found this post:

Using SELECTEDVALUE with Fields Parameters in Power BI - SQLBI

WHERE __SelectedValue =

SELECTCOLUMNS (

SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),

Parameter[Parameter]

)

RETURN CONCATENATEX ( __SelectedValue, Parameter[Parameter], ", " )

But I don't know where the Parameter Fields field comes from and the summarize function gives me an error with this syntax.

Thank you very much for your attention.

Greetings

[MFelix]

Hi @Xavier2609 ,

Has refered you need to create a calculated measure and not a calculated column, slicers (from parameters or other type of values) cannot be used in tables/columns directly.

[v-yiruan-msft · Accepted solution]

Hi @Xavier2609 ,

What you are creating is a calculated column, and that what-if parameter was used in this new created calculated column? If yes, as the value of a calculated column is computed during data refresh and uses the current row as a context, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report. So it will not return your expected result, when switch the value of what-if parameter in the calculated column... If you want to get the TOPN and others items dynamically, you can refer the following links to get it by creating measure +what-if parameter or TOPN filter function.

Filter top 3 products with a row for others using DAX in Power BI

DAX Fridays! #132: Dynamic TopN + Others in Power BI

In addition, you can review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

There is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

Best Regards

[MFelix]

Hi @Xavier2609 ,

In this case you need to create a what if parameter and then use the metric that is created automatically to change by the quantity part of the measure.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if