Dynamic Calculated Column Based On Parameter Selection

[DW868990]

Hi,

I am looking to create a stand alone table in my model which is a dynamic column which is a list of numbers in which the bin size between the numbers is determined by a what-if parameter. 

So if user selects 50,

it would produce a list like 0, 50, 100, 150, 200 etc 

If 100 is selected it would produce 0, 100, 200, 300 etc

It is seeming to be difficult then I initially expected.

Any help appreciated.

Thanks

JJ

[Barthel · Accepted solution]

Hey @DW868990,

Unfortunately, it is not possible to calculate a table dynamically based on a parameter selection. We can precalculate a number of possible parameter selections and then filter these, so that the relevant table remains for the selection. For this we need two columns: one that refers to the parameter selection of the user and one column with its output. Here is an example of such a calculated table:

Table =
VAR _start = 1
VAR _end = 100
VAR _increment = 1
VAR _rep = 10
RETURN
    GENERATE (
        SELECTCOLUMNS (
            GENERATESERIES ( _start, _end, _increment ),
            "user_value", [Value]
        ),
        VAR _value = [user_value]
        RETURN
            GENERATESERIES ( 0, 1 + _value * _rep, _value )
    )

_start is the start value of the parameter that a user can select. _end is the end value. Increment is the increment between the start and end value. _rep is how many times the output value is repeated based on the parameter selection. This is an example of the table output, with the user selecting 100: