Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

DAX - Create a new table that repeats the value based on another column

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:

 

ProductPallet Quantity
A3
B2

 

Desired Result:

 

ProductPallet Quantity
A1
A1
A1
B1
B1

 

How can I do that in DAX?

 

Thank you in advance!

 

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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]
    )

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Ahmedx
Super User
Super User

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
Screen Capture #535.pngScreen Capture #536.png

View solution in original post

4 REPLIES 4
Ahmedx
Super User
Super User

also possible in power query
Screen Capture #538.pngScreen Capture #539.pngScreen Capture #542.pngScreen Capture #543.pngScreen Capture #544.png

Ahmedx
Super User
Super User

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
Screen Capture #535.pngScreen Capture #536.png

Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

@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]
    )

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors