Forum Discussion

Steven-conance's avatar
3 years ago
Solved

Multiple GENERATESERIES columns in 1 table

Just curious if this is even possible;   I've got a data model with multiple tables with only 1 column, constructed using GENERATESERIES. These multiple tables all have one column, the number of r...
  • AlexisOlson's avatar
    3 years ago

    Here's a another approach:

    Table = 
        VAR MaxPrice_Start = 0
        VAR MaxPrice_End = 100
        VAR MaxPrice_Incr = 10
        VAR MaxPrice = GENERATESERIES ( MaxPrice_Start, MaxPrice_End, MaxPrice_Incr )
        VAR MinAmount_Start = 10
        VAR MinAmount_End = 20
        VAR MinAmount_Incr = 2
        VAR MinAmount = GENERATESERIES ( MinAmount_Start, MinAmount_End, MinAmount_Incr )
        VAR TotalRows = MAX ( COUNTROWS ( MaxPrice ), COUNTROWS ( MinAmount ) )
        VAR IndexCol = GENERATESERIES ( 0, TotalRows - 1 )
    RETURN
        SELECTCOLUMNS (
            IndexCol,
            "Index", [Value],
            "Images - Maximum Selling Price (incl. VAT)",
                IF (
                    MaxPrice_Start + MaxPrice_Incr * [Value] <= MaxPrice_End,
                    MaxPrice_Start + MaxPrice_Incr * [Value]
                ),
            "Images - Minimum Amount",
                IF (
                    MinAmount_Start + MinAmount_Incr * [Value] <= MinAmount_End,
                    MinAmount_Start + MinAmount_Incr * [Value]
                )
        )

     

  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    I'm not sure what you're doing with these but if you're using them as parameters for slicers, then I'd advise against combining them into one table. It might seem cleaner that way but they would become interdependent (using one column filters the other columns in the same table).

     

    If you do need them in a single table for some reason, I'd suggest doing it in M instead of DAX since the syntax is so much simpler using Table.FromColumns:

    let
        MaxPrice  = List.Numbers(0, 1000, 10),
        MinAmount = List.Numbers(0, 20, 1),
        Result =
            Table.FromColumns(
                {MaxPrice, MinAmount},
                {"Images - Maximum Selling Price (incl. VAT)", "Images - Minimum Amount"}
            )
    in
        Result