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 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
" Slicer"
"Dynamic Slicer"
"FILTER"

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    AmiraBedh
    Super 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