DAX command to duplicate rows

[shanobkottal]

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

[OwenAuger]

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

[shanobkottal · 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

[OwenAuger]

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.

[shanobkottal]

Thank you. This formula does work .