Forum Discussion

Sakshi's avatar
Sakshi
Helper I
9 years ago
Solved

To display repeated rows using DAX

Hello All

 

I am a newbie in DAX. Need to figure out the implementation of looping concept.

 

I have a single table which has columns as

  • Start-Date 
  • No. of Repeating days
  • Assigned Value

Now I need to display a single tabular visualization in Power BI Report displaying all the dates with their assigned values. 

Let's suppose Start-Date = "01 Aug 2017" and No. of Repeating Days =3 for that row having Assigned Value = 4. Then, the table should be like:

 

Date                        Assigned Value

01 Aug 2017                   4

02 Aug 2017                   4

03 Aug 2017                   4

 

Could someone please direct me how to achieve this through DAX or through Power BI features.

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Sakshi,

     

    Based on my test, you should be able to follow steps below to get your expected result in Power BI. :smileyhappy:

     

    1. In Query Editor, use the formula(M) below to add a new custom column called "End-Date" under Add Column tab > Custom Column.

    = Date.AddDays([#"Start-Date"],[No. of Repeating days]-1)

     

    2. Use the formula(M) below to add another custom column called "Date".

    = {Number.From([#"Start-Date"])..Number.From([#"End-Date"])}

     

    3. Expand to New Rows for Date column.

     

    Then you will get a table like below.

     

    4. Last, you will get your expected result by changing Data Type to Date for "Date" column.

     

    5. Click "Close&Apply" to apply the changes.

     

    Regards

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Sakshi,

     

    Based on my test, you should be able to follow steps below to get your expected result in Power BI. :smileyhappy:

     

    1. In Query Editor, use the formula(M) below to add a new custom column called "End-Date" under Add Column tab > Custom Column.

    = Date.AddDays([#"Start-Date"],[No. of Repeating days]-1)

     

    2. Use the formula(M) below to add another custom column called "Date".

    = {Number.From([#"Start-Date"])..Number.From([#"End-Date"])}

     

    3. Expand to New Rows for Date column.

     

    Then you will get a table like below.

     

    4. Last, you will get your expected result by changing Data Type to Date for "Date" column.

     

    5. Click "Close&Apply" to apply the changes.

     

    Regards