Forum Discussion
Create Sequential Numbering Between Values in a column
Hi,
I am trying to create sequential values between two values per group.
Column y = group
Column x = Fill between values
Here's the source table created in Power Query:
Here's the expected table. (Preferably created with DAX)
Note: The column x fill values in the expected table are incremented by 0.25.
0.25 is a variable. It could also be 0.5 or 1.0
The main challenge is filling the values between minimum and maximum value per group.
Is there a way to do this in DAX?
I've also attached the .pbix
https://drive.google.com/file/d/1rA6YdNJe5R1cySDKCOn6UiLAw_sqGb5g/view?usp=sharing
Thank You.
Hi r_cruiser ,
I have created a simple sample, please refer to it to see if it helps you.
Create a table.
Table = SELECTCOLUMNS ( FILTER ( GENERATE ( Src_Table, GENERATESERIES ( MIN ( Src_Table[Group_MinX] ), MAX ( Src_Table[Group_MaxX] ), 0.25 ) ), [Value] >= Src_Table[Group_MinX] && [Value] <= Src_Table[Group_MaxX] ), "Label", Src_Table[y], "Value", [Value] )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi r_cruiser ,
I have created a simple sample, please refer to it to see if it helps you.
Create a table.
Table = SELECTCOLUMNS ( FILTER ( GENERATE ( Src_Table, GENERATESERIES ( MIN ( Src_Table[Group_MinX] ), MAX ( Src_Table[Group_MaxX] ), 0.25 ) ), [Value] >= Src_Table[Group_MinX] && [Value] <= Src_Table[Group_MaxX] ), "Label", Src_Table[y], "Value", [Value] )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Thank you. That solution helped out almost immediately. It took me some review time to understand the syntax but I get it now.
Thank You again!
r_cruiser You want to use GENERATESERIES. GENERATESERIES function - DAX | Microsoft Learn
I understand the concept of GENERATESERIES, but do not know the implementation for my source table to end with the expected table.
GENERATESERIES. It would seem to me as if I need a loop to generate a series of values between two known values for each group to construct a single table.
Is there a way to generate a series for each group? It's not straight forward to me. Not yet.r_cruiser Something like this:
Table 2 = GENERATE( DISTINCT('Table'[y]), GENERATESERIES(MAX('Table'[Group_MinX]), MAX('Table'[Group_MaxX]), .25) )It's the basic technique from Blowout!
Blowout! - Microsoft Power BI Community
Each group have different start and end values. GENERATESERIES accepts an absolute single value. Hmmm.
There's a way to do it in Power Query, but creating it in DAX perplexes me.
This the solution in Power Query. This is not really solved as the OP was asking it to be done in DAX.
Much like I am asking.
Solved: GENERATESERIES OVER A GROUP in POWER BI using DAX? - Microsoft Power BI Community
I perfer it to be done in DAX to decrease load times. In Power Query load times could be as much as few hundred thousand lines. At time maybe up to 1M lines.
