Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

for loops to generate a dataframe

Hi Guys, 
given this code:

NewTable =
ADDCOLUMNS (
    SUMMARIZE ( table, table[Date] ),
    "AP_0", [AP_0]
)
I need to make it dynamic in the following sense. I created a measure called AP which depends on an index j, now I would need to add a column 
''j'', [AP]
for each of those j.
It is impossible to do it by hand because I hacve 1000 indices but I didnt find a way to add columns dynamically.
Need Help

3 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    2 years ago

    Are you sure this needs to be recursive? You could start with a virtual table in a variable with your index values (j, k, ...) and use ADDCOLUMNS with your measure to determine it in that row context.

     

    If it truly needs to be recursive, it is not practical in DAX for a high number of cycles, but you can do it in Power Query with List.Generate or List.Accumulate (or a custom function).

     

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    Can you provide some sample data and the output you want, so that can provide more suggestion for you.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    yes here is the problem I have.

    The starting dataset is a 3 column table: 'date', 'simulation number', 'value'.
    I have a measure which perform an operation over the value column filtering for sim number, so for each sim number I compute this operation (which is time granulation sensitive).

    The measure is the following:

    _AP = VAR j = SELECTEDVALUE(IndexTable[Value])
    RETURN
    SUMX(
    maybe_right,
    CALCULATE(
    SUMX(maybe_right, maybe_right[capacity]*maybe_right[price])/[_sum_cf], FILTER(ALLEXCEPT(maybe_right, maybe_right[Date]), maybe_right[simulation] = j
    )
    ))

    The nest phase would be to compute the quantile (worst 1%, 5% ecc) for those results and my idea was to add the results of the measure to a new table in which each column is one simulation.

    Actually I menaged to do it but the problem is that it is not time sensitive anymore (which it makes sense!), so I was trying to find a way to do it all in one measure: compute the reaults for each index and directly compute the quantile and plot it.
    I hope I have been clear.
    Thank you