Forum Discussion

DYY94's avatar
DYY94
Helper I
3 years ago

Dupicate rows using measure

Hello Community, 

 

Is it possible to duplicate rows using a Measure.  

 

This is my measure : 

 

 

 

dates =
VAR str = "2028/04/13-5"
VAR _date =
    FORMAT ( LEFT ( str, 10 ), "yyyy/mm/dd" )
VAR _num =
    RIGHT ( str, 1 )
VAR _cal =
    MID ( str, 11, 1 )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] )
            >= YEAR ( _date ) - _num
            && YEAR ( [Date] ) < YEAR ( _date )
            && MONTH ( [Date] ) = MONTH ( _date )
            && DAY ( [Date] ) = DAY ( _date )
    )
VAR _a =
    CONCATENATEX ( tmp, [Date], "
" )
RETURN
    SWITCH ( TRUE (), _cal = "-", _a )

 

 

 

 

If you want to test it , just create this table before:

 

 

Table = CALENDAR(DATE(2022,1,1),DATE(2028,04,13))

 

 

 

- this is the actual output : 

 

 

- Expected Ouput: 

 

 

I want split this dates in rows with the same Code_ISIN.

 

Thanks. 

Need Help

6 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, DYY94 ;

    If you want to increase the number of rows, may be we need create a new table by dax such as:

    New = 
GENERATE(
SUMMARIZE('Table',[Code], [Maturatie],[Mr/365]),
FILTER(
CALENDAR(
    DATE(YEAR([Maturatie])-[Mr/365],MONTH([Maturatie]),DAY([Maturatie])),
    DATE(YEAR([Maturatie])-1,MONTH([Maturatie]),DAY([Maturatie]))
),MONTH([Date])=MONTH([Maturatie])&&DAY([Date])=DAY([Maturatie])))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • DYY94's avatar
      DYY94
      Helper I
      3 years ago

      Hi v-yalanwu-msft ,

       

      Thanks for the solution it's great . 

       

      I have only one issue with [MR/365] in my case it's a measure and i can't included in in Summrize ! 

      when i replaced with yours it didn't do the calculation. 

       

      Here screenshots below : 

       

       

       

       

      Hope you understand me and Thanks again. 

      v-yalanwu-msft  !

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, DYY94 ;

    You could try it.

    New =
GENERATE (
    SUMMARIZE ( 'Table', [Code], [Maturatie], "1", [Mr/365] ),
    FILTER (
        CALENDAR (
            DATE ( YEAR ( [Maturatie] ) - [Mr/365], MONTH ( [Maturatie] ), DAY ( [Maturatie] ) ),
            DATE ( YEAR ( [Maturatie] ) - 1, MONTH ( [Maturatie] ), DAY ( [Maturatie] ) )
        ),
        MONTH ( [Date] ) = MONTH ( [Maturatie] )
            && DAY ( [Date] ) = DAY ( [Maturatie] )
    )
)

    If the above one can't help you get the desired result,It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    Solved: How to upload PBI in Community - Microsoft Power BI Community


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • DYY94's avatar
      DYY94
      Helper I
      3 years ago

      Hi v-yalanwu-msft , 

       

      Thanks again for you help ! 

       

      I tried you last code , it works fine but it give me always number  '1' for all colmun instead the real numbers from [MR/365].  

       

      Ouput : 

       

       

      [MR/365] : 

       

       

      Thanks Again ! 

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, DYY94 ;

    It is better if you can share a simplified pbix file after removing sensitive information.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.