Forum Discussion

DataNinja777's avatar
DataNinja777
Super User
2 years ago
Solved

Generateseries of multiple values

Hi Power BI Community,   I'd like to generateseries from the table below using the month number as the input value of end_value in the syntax below.   GENERATESERIES ( start_value, end_value [,...
Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    This topic was solved by simplifying the code in the earlier topic as follows:

    Series = 
GENERATE(
    VALUES('Tasks'[Task]),  -- Iterate over distinct Task Names
    VAR MaxFinalLife = MAXX(FILTER('Tasks', 'Tasks'[Task] = EARLIER('Tasks'[Task])), 'Tasks'[Final Life])
    RETURN
    SELECTCOLUMNS(
        GENERATESERIES(1, MaxFinalLife, 1),
        "Series", [Value]
    )
)

     

  • danextian's avatar
    danextian
    2 years ago

    Hi DataNinja777 ,

     

    This formula refers to an existing table.

    Series = 
VAR __TBL =
    SELECTCOLUMNS('Data', "Start Date", 'Data'[Start Date], "End Date", 'Data'[End Date], "Index", 'Data'[Index] )
VAR __MAX = MAX ( Data[Month] )
VAR __Series =
    GENERATESERIES ( 1, __MAX, 1 ) --must use the highest month count
VAR __CROSSJOINED =
    ADDCOLUMNS (
        CROSSJOIN ( __TBL, __Series ),
        "Date", EDATE ( [Start Date], [Value] ) - 1
    ) -- this will generate dates way later than the end date
RETURN
    FILTER ( __CROSSJOINED, [Date] <= [End Date] )

    Index is not necessary. It is just a way for me to identify which row from the referenced table a row in the series table is referring to.  Be warned that doing this on a large table may cause a performance issue.

DataNinja777's avatar
DataNinja777
Super User
2 years ago

Hi danextian ,

 

Thank you for your input, however, the two lines are only simplified example, and I could have thousand lines for that, so typing hardcoded numbers is not an option for me.  I was thinking something in line of the solution below by xifeng_L , but a much simpler scenario than the case below, as I don't need to make other inputs in the generateseries syntax dynamic.  

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/I-want-to-generate-a-series-with-multiple-start-and-end-values/m-p/3964269

Best regards,

DataNinja777's avatar
DataNinja777
Super User
2 years ago

This topic was solved by simplifying the code in the earlier topic as follows:

Series = 
GENERATE(
    VALUES('Tasks'[Task]),  -- Iterate over distinct Task Names
    VAR MaxFinalLife = MAXX(FILTER('Tasks', 'Tasks'[Task] = EARLIER('Tasks'[Task])), 'Tasks'[Final Life])
    RETURN
    SELECTCOLUMNS(
        GENERATESERIES(1, MaxFinalLife, 1),
        "Series", [Value]
    )
)