input values for a new calculated table

[RobertaCC]

Hi everybody! I have built the script below and it works. What I need is to turn the var "range" into a variable that users select.

I tried building a parameter and even another query with all the values the user can choose upon and connecting the parameter table/query to the var range through the function SELECTEDVALUE but this function always gives me a blank result.

I thought that I could have built the table for any possibile value of the var range but I do not know how to built the recurring computation for any possibile value of the variable range (as done with index). Can you help me please?

Tabella fasce =

var range = 20

var val_min_start = CALCULATE(MIN(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))

var val_min_end = CALCULATE(MAX(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))-range

var val_max_start = CALCULATE(MIN(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))+range

var val_max_end = CALCULATE(MAX(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))

var val_min = GENERATESERIES(val_min_start,val_min_end,range)

var val_max = GENERATESERIES(val_max_start,val_max_end,range)

var tot_rows = max(COUNTROWS(val_min),COUNTROWS(val_max))

var index_col = GENERATESERIES(0,tot_rows)

var tab = ADDCOLUMNS(

    SELECTCOLUMNS(

        index_col,

        "index",[Value],

        "min1",

            if(

                val_min_start+range*[Value]<=val_min_end,

                val_min_start+range*[Value]

            ),

        "max2",

            if(

                val_max_start+range*[Value]<=val_max_end,

                val_max_start+range*[Value]

            )  

    ),

    "fascia", concatenate(CONCATENATE(TRUNC([min1],2),"-"),TRUNC([max2],2)))

var prezzo = SUMMARIZECOLUMNS(Foglio1[RICAVO TOTALE UNITARIO])

var finale = CROSSJOIN(tab,prezzo)

return finale

[Anonymous]

Hi @RobertaCC 

Thanks to @AmiraBedh for the prompt reply.

I have another idea though. If your purpose is to create a calculated table and let report users can switch the value provided for the variable "range" via perhaps a slicer, this is not possible currently. A calculated table is evaluated when it's created or refreshed, make it looks "static". It cannot be affected dynamically by a slicer or filter from the report, so it cannot be affected by users. That's why you find the function SELECTEDVALUE always gives you a blank result as it doesn't get a value selected from a slicer/filter but gets the altervative "blank" value from the table which has all possible values. 

Only measures can be affected by filters/slicers which can be used by report users. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[AmiraBedh]

Enter the range values you want to provide as options 10, 20 and 30.

Next, you need to create a slicer in your report to allow users to select a range value.

Then modify your DAX : 

Tabella fasce =
var range = SELECTEDVALUE(RangeParameter[Range], 20)
var val_min_start = CALCULATE(MIN(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))
var val_min_end = CALCULATE(MAX(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))-range
var val_max_start = CALCULATE(MIN(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))+range
var val_max_end = CALCULATE(MAX(Foglio1[RICAVO TOTALE UNITARIO]),ALL(Foglio1))
var val_min = GENERATESERIES(val_min_start,val_min_end,range)
var val_max = GENERATESERIES(val_max_start,val_max_end,range)
var tot_rows = max(COUNTROWS(val_min),COUNTROWS(val_max))
var index_col = GENERATESERIES(0,tot_rows)
var tab = ADDCOLUMNS(
    SELECTCOLUMNS(
        index_col,
        "index",[Value],
        "min1",
            if(
                val_min_start+range*[Value]<=val_min_end,
                val_min_start+range*[Value]
            ),
        "max2",
            if(
                val_max_start+range*[Value]<=val_max_end,
                val_max_start+range*[Value]
            )  
    ),
    "fascia", concatenate(CONCATENATE(TRUNC([min1],2),"-"),TRUNC([max2],2)))
var prezzo = SUMMARIZECOLUMNS(Foglio1[RICAVO TOTALE UNITARIO])
var finale = CROSSJOIN(tab,prezzo)
return finale