topic Re: Create the new table with DAX considering Space. in DAX Commands and Tips

Create the new table with DAX considering Space.

ChoiJunghoon — Tue, 06 Jul 2021 08:37:43 GMT

Hello.
Can Dax create the below the table ?

TableA

SpaceNumber

TableB

Category	SpaceNumber	Cycle
A	1	5
B	2	5
C	3	4
D	1	4
E	2	3
F	3	4
G	1	4
H	2	5
I	3	3

ResultTable

Category	SpaceNumber	CycleIndex
A	1	1
A	1	2
A	1	3
A	1	4
A	1	5
B	2	1
B	2	2
B	2	3
B	2	4
C	3	1
C	3	2
C	3	3
C	3	4
D	1	6
D	1	7
D	1	8
D	1	9
E	2	6
E	2	7
E	2	8
F	3	5
F	3	6
F	3	7
F	3	8
G	1	10
G	1	11
G	1	12
G	1	13
H	2	9
H	2	10
H	2	11
H	2	12
H	2	13
I	3	9
I	3	10
I	3	11

Re: Create the new table with DAX considering Space.

MattAllington — Tue, 06 Jul 2021 09:48:01 GMT

Not DAX, but yes to power query.
load table B to PQ

add a new column that creates a a list from 1 to the value in number cycle. It would look something like this.

={1..[NumberCycle]}

this will give a column containing lists. You should be able to expand this column into new rows.

Re: Create the new table with DAX considering Space.

Anonymous — Tue, 06 Jul 2021 13:37:15 GMT

define table TableB = DATATABLE( "Category", STRING, "SpaceNumber", INTEGER, "Cycle", INTEGER, { {"A", 1, 5}, {"B", 2, 4}, {"C", 3, 4}, {"D", 1, 4}, {"E", 2, 3}, {"F", 3, 4}, {"G", 1, 4}, {"H", 2, 5}, {"I", 3, 3} } ) EVALUATE SELECTCOLUMNS( GENERATE( TableB, var Cycle_ = TableB[Cycle] var SpaceNumber_ = TableB[SpaceNumber] var Category_ = TableB[Category] var CycleIndexStart = 1 + SUMX( filter( TableB, TableB[SpaceNumber] = SpaceNumber_ && TableB[Category] < Category_ ), TableB[Cycle] ) var CycleIndex_ = GENERATESERIES( CycleIndexStart, CycleIndexStart + Cycle_ - 1, 1 ) return CycleIndex_ ), "Category", TableB[Category], "SpaceNumber", TableB[SpaceNumber], "CycleIndex", [Value] )

Execute this code in DAX Studio... By the way, your result table from above has mistakes in it. Mine seems to work correctly.