Forum Discussion

RobertaCC's avatar
RobertaCC
Frequent Visitor
2 years ago

input values for a new calculated table

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 th...
" Slicer"
"Dynamic Slicer"
"FILTER"
AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
2 years ago

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