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 for the Category field. Is it possible to do in DAX?

 

I could have done in M query, but then Table A is derived using DAX and moving back to power query and rebuilding there is a tough work.

 

Thank you

Table A:

AMinMax
Apple3/1/20233/5/2023
Orange2/1/20232/5/2023
Banana1/1/20231/5/2023

 

 

Output Calculated Table:

 

ADate
Apple3/1/2023
Apple3/2/2023
Apple3/3/2023
Apple3/4/2023
Apple3/5/2023
Orange2/1/2023
Orange2/2/2023
Orange2/3/2023
Orange2/4/2023
Orange2/5/2023
Banana1/1/2023
Banana1/1/2023
Banana1/3/2023
Banana1/4/2023
Banana1/5/2023
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

6 Replies

  • 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

      • 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)
)

         

    • anvikuttu's avatar
      anvikuttu
      Advocate I
      3 years ago

      Thank you, however as mentioned in my msg i would like to do this in DAX

  • 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: