The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
7 | |
6 |