Forum Discussion

Prabhu_MDU's avatar
Prabhu_MDU
Advocate I
7 years ago
Solved

how to repeat the rows in a Datatable based on a column value

Hi, 

 

as per the image given below.. 

I want to repeat the row based on the StartDate and EndDate columns. 

 

In the Image:

the first output tells you the exact out put from the Databse tables.. 

the second output tells you the calculated out put from the Database tables..

 

what am I doing here ?.. 

well,

1) I am just calculating the calendar month which lies from the start date and ends before the Enddate

2) All the derived date values are End Of Month values.. 

3) Except the EOMDate colums, all the rest of the columns will remain the same.. 

 

what is expected !!

I want to know how can I do the same in power bi using DAX Queries.

 

Any help is much appreciated.

 

Thanks & Regards, 

Prabhu

 

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Prabhu_MDU,

     

    You may refer to steps below:

     

    Firstly, click Modeling-> New Table, repeat current rows using DAX below:

    Table = 
UNION (
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1
)

     Secondly, create a calendar table which contains a series of date which is end of month:

    Calendar = 
UNION (
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 0 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 1 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 2 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 3 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 4 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 5 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 6 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 7 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 8 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 9 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 10 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 11 ) )
)

    Finally, generate the result table using DAX below:

    Result = 
DISTINCT (
    SELECTCOLUMNS (
        CROSSJOIN ( 'Table', 'Calendar' ),
        "CLIENT_POLICY_NUMBER", 'Table'[CLIENT_POLICY_NUMBER],
        "StartDate", 'Table'[StartDate],
        "EndDate", 'Table'[EndDate],
        "PolicyYear", 'Table'[PolicyYear],
        "InforceDate", 'Table'[InforceDate],
        "CancelDate", 'Table'[CancelDate],
        "INSURED", 'Table'[INSURED],
        "DOM_STA", 'Table'[DOM_STA],
        "PROGRAM", 'Table'[PROGRAM],
        "PRODUCT", 'Table'[PRODUCT],
        "Company", 'Table'[COMPANY],
        "MGA_NAM", 'Table'[MGA_NAM],
        "Total_Poli", 'Table'[Total_Poli],
        "ProfitCen", 'Table'[ProfitCen],
        "EOMDate", 'Calendar'[EOMDate]
    )
)

      

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Prabhu_MDU,

     

    You may refer to steps below:

     

    Firstly, click Modeling-> New Table, repeat current rows using DAX below:

    Table = 
UNION (
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1,
    Table1
)

     Secondly, create a calendar table which contains a series of date which is end of month:

    Calendar = 
UNION (
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 0 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 1 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 2 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 3 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 4 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 5 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 6 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 7 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 8 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 9 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 10 ) ),
    ROW ( "EOMDate", EOMONTH ( "02/28/2018", 11 ) )
)

    Finally, generate the result table using DAX below:

    Result = 
DISTINCT (
    SELECTCOLUMNS (
        CROSSJOIN ( 'Table', 'Calendar' ),
        "CLIENT_POLICY_NUMBER", 'Table'[CLIENT_POLICY_NUMBER],
        "StartDate", 'Table'[StartDate],
        "EndDate", 'Table'[EndDate],
        "PolicyYear", 'Table'[PolicyYear],
        "InforceDate", 'Table'[InforceDate],
        "CancelDate", 'Table'[CancelDate],
        "INSURED", 'Table'[INSURED],
        "DOM_STA", 'Table'[DOM_STA],
        "PROGRAM", 'Table'[PROGRAM],
        "PRODUCT", 'Table'[PRODUCT],
        "Company", 'Table'[COMPANY],
        "MGA_NAM", 'Table'[MGA_NAM],
        "Total_Poli", 'Table'[Total_Poli],
        "ProfitCen", 'Table'[ProfitCen],
        "EOMDate", 'Calendar'[EOMDate]
    )
)

      

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.