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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.