Forum Discussion
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
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
4 Replies
Hello Anonymous,
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
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
