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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 


@ 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!:
Mastering Power BI 2nd Edition

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

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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