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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
shanobkottal
Frequent Visitor

DAX command to duplicate rows

Hello,  I am trying to find a way to duplicate rows in the fact table using DAX (Not via power Query) in a calculated column . I need rows which have count column > 1 to be duplicated by the number of times the count represents.  Image below. Would appreciate any gudiance here

Image.PNG

 

 

 

 

1 ACCEPTED SOLUTION

Thank you Owen. I will try your solution out. 

 

The test data that I have listed is just for simplicty ; The actual use case is that we have an initiatives around sales that drive revenue. Each row in the fact table represents a sale . Sometimes one of these rows can be driven by two or more initiatives ( count > 1 represents the nunber of initiatves that drove the sale). We need to attribute certain % of revenue to each initiative. Duplicating the rows would enable me to use %`s to attribute revenue. 

P.S I gave Count as number for simplicty. actual data has count as text values  segregated by delimiter for eg DigitalMarketing/SalesDriven/EmailCampaign.  Duplicating the rows & little bit of dax will enable me to split one row and then each row will have one initiative & associated % . This will help me use it in reporting/filters etc.

 

Initially i thought aboout calcuated table , but was not sure about it because now I will have to connect all the dimesnion table to teh new calcuated table again. The original table has values beign pulled in from teh Dim tables using Related

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hello @shanobkottal,

 

Out of interest, what's the context for creating this duplicated table, and how would it be used?

Also, I wasn't sure what you meant by "in a calculated column".

 

In any case, you could create a table with duplicated rows, referencing the original 'Fact' with an expression like this:

 

Fact Duplicated = 
GENERATE (
    'Fact',
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 'Fact'[Count] ),
        "Index", [Value]
    )
)

 

The resulting table will have an additional Index column, with values 1..Count for each original row.

You could use SELECTCOLUMNS to retain just the required columns if needed.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you Owen. I will try your solution out. 

 

The test data that I have listed is just for simplicty ; The actual use case is that we have an initiatives around sales that drive revenue. Each row in the fact table represents a sale . Sometimes one of these rows can be driven by two or more initiatives ( count > 1 represents the nunber of initiatves that drove the sale). We need to attribute certain % of revenue to each initiative. Duplicating the rows would enable me to use %`s to attribute revenue. 

P.S I gave Count as number for simplicty. actual data has count as text values  segregated by delimiter for eg DigitalMarketing/SalesDriven/EmailCampaign.  Duplicating the rows & little bit of dax will enable me to split one row and then each row will have one initiative & associated % . This will help me use it in reporting/filters etc.

 

Initially i thought aboout calcuated table , but was not sure about it because now I will have to connect all the dimesnion table to teh new calcuated table again. The original table has values beign pulled in from teh Dim tables using Related

Thanks for the further explanation 🙂

 

I would recommend that this sort of splitting/allocation is done before the table arrives in the data model, either in data source or Power Query. It's unusual to use DAX for this sort of operation, and as you've mentioned, you end up having to re-create all relationships with the new table, as well as having a possibly redundant table left in the model.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you. This formula does work .

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors