Forum Discussion

Steven-conance's avatar
Steven-conance
Icon for Helper I rankHelper I
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...
DAX
  • AlexisOlson's avatar
    AlexisOlson
    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

     

Steven-conance's avatar
Steven-conance
Icon for Helper I rankHelper I
3 years ago

Wow. I'm surprised by your creativity, both! Pretty cool to see what is possible. 

However this also tells me this desire to stick it all in one table probaby isn't the way to go. I'd loose a couple of simple tables, in exchange for a (for me) hard to maintain piece of code. 

Especially considering that already now it's  a tough one, with only 2 columns. And I have about 5 of these GENERATESERIES tables.. 

 

Might acutally be better to create the tables in Power Query / Sql / Excel and merge them before moving to the data model. 

 

Which route would you choose Greg_Deckler  AlexisOlson 
So far there hasn't been a need to adjust the series after initial load, so something less flexible than DAX Would be perfectly fine

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
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