Forum Discussion

anvikuttu's avatar
anvikuttu
Advocate I
3 years ago
Solved

DAX question

Hi Team, I have trouble transforming Table A to the Output Calculated Table .  I wanted for every category and based on Min and Max dates, i want to generate consecutive dates between Min and Max fo...
Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey anvikuttu ,

     

    using GENERATE and GENERATESERIES does the trick:

     

    Table 2 = 
SELECTCOLUMNS(
    GENERATE(
        'Table'
        , var startValue = [Min]
        var endValue = [Max]
        return
        GENERATESERIES( startValue , endValue , 1 )
    )
    , "A" , [A]
    , "Date" , [Value]
)

     

    A screenshot:


    Hopefully, this provides what you are looking for.

    Nevertheless, you must consider that DAX tables will not benefit from all compressions that are happening during data load/data refresh. If the table becomes large, you might encounter performance degradation when this table is used inside measures.

    Regards,

    Tom

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi anvikuttu 

you would need a date table to help, try create one like:

Dates = CALENDAR(MIN(TableA[Min]), MAX(TableA[Max]))

 

then write a calculated table like:

Table2 = 
GENERATE(
    VALUES(TableA[A]),
    VAR MinDate = CALCULATE(MIN(TableA[Min]))
    VAR MaxDate = CALCULATE(MAX(TableA[MAX]))
    RETURN
        CALCULATETABLE(
            VALUES(Dates[Date]),
            Dates[Date]>=MinDate,
            Dates[Date]<=MaxDate
        )
)

 

it worked like: