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

TomMartens's avatar
TomMartens
Super User
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

anvikuttu's avatar
anvikuttu
Advocate I
3 years ago

Thank you TomMartens , I agree with your statement. In my case the data is not much.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    tried to imitating  TomMartens' solution:

    Table4 = 
GENERATE(
    VALUES(TableA[A]),
    VAR MinDate = CALCULATE(MIN(TableA[Min]))
    VAR MaxDate = CALCULATE(MAX(TableA[MAX]))
    RETURN
        GENERATESERIES(MinDate, MaxDate)
)