Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hello.
Can Dax create the below the table ?
TableA
SpaceNumber |
3 |
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 |
Solved! Go to Solution.
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.
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.
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.
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.
User | Count |
---|---|
13 | |
8 | |
8 | |
7 | |
5 |
User | Count |
---|---|
21 | |
15 | |
15 | |
10 | |
7 |