Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Dear PBI users, good morning.
I need a help with this issue, I would like to create a new table using dax that would repeat the value of another column based on another. To exemplify:
Current table:
Product | Pallet Quantity |
A | 3 |
B | 2 |
Desired Result:
Product | Pallet Quantity |
A | 1 |
A | 1 |
A | 1 |
B | 1 |
B | 1 |
How can I do that in DAX?
Thank you in advance!
Solved! Go to Solution.
@Anonymous Try this. PBIX is attached below signature.
Table 2 =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATE(
DISTINCT('Table'[Product]),
GENERATESERIES(1,MAXX(FILTER('Table',[Product] = EARLIER('Table'[Product])),[Pallet Quantity]),1)
),
"Pallet Quantity",1
),
"Product",[Product],
"Pallet Quanitity",[Pallet Quantity]
)
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhjeqxmvgzEItNzcu?e=frRM7C
also possible in power query
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhjeqxmvgzEItNzcu?e=frRM7C
Is there a way that I could include Indexes as well?
For example
Product - Pallet Quantity - Index
A - 1 - 1
A - 1 - 2
A - 1 - 3
B - 1 - 1
B - 1 - 2
@Anonymous Try this. PBIX is attached below signature.
Table 2 =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATE(
DISTINCT('Table'[Product]),
GENERATESERIES(1,MAXX(FILTER('Table',[Product] = EARLIER('Table'[Product])),[Pallet Quantity]),1)
),
"Pallet Quantity",1
),
"Product",[Product],
"Pallet Quanitity",[Pallet Quantity]
)
User | Count |
---|---|
75 | |
75 | |
45 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |