Forum Discussion
Duplicate, shift, then aggregate
Suppose there is Base Table like below
I would like an output table like the left side of below.
Repeat and Offset are variables or could be parameters in Power BI. Not sure "Offset" is the best name, though.
I am wondering this can be done by using combinations of Window functions, but not so sure yet. Any advice or even inspirations would be appreciated!
Hi Kazu,
By utilizing the DAX function GENERATE along with a parameter table or offset table, you can dynamically control the offset value. Please use the following DAX as a calculated table to achieve the desired outcome.
OutputTable =
GENERATE(
'Base',
ADDCOLUMNS(
'OffsetTable',
"ShiftedIndex", 'Base'[Index] + 'OffsetTable'[OffsetValue],
"ShiftedValue", 'Base'[Value]
)
)Thank you.
5 Replies
Hi Kazu ,
You are on the right track with OFFSET. Here is a video with more information:
https://www.youtube.com/watch?v=BRZrC3stCEQ
Thanks. I am trying OFFSET and I feel it would work if the number of repeat in my example is fixed but not if variable.
Hi Kazu,
By utilizing the DAX function GENERATE along with a parameter table or offset table, you can dynamically control the offset value. Please use the following DAX as a calculated table to achieve the desired outcome.
OutputTable =
GENERATE(
'Base',
ADDCOLUMNS(
'OffsetTable',
"ShiftedIndex", 'Base'[Index] + 'OffsetTable'[OffsetValue],
"ShiftedValue", 'Base'[Value]
)
)Thank you.
Hi Kazu,
Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.
Thank you.
Hi Kazu,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
