Multiple GENERATESERIES columns in 1 table

[Steven-conance]

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 rows varies. Two examples: 

1. Images - Maximum Selling Price (incl. VAT) = GENERATESERIES(0, 10000, 10)
2. Images - Minimum Amount = GENERATESERIES(0, 20, 1)

   and so on.

Is it possible to do this all within one new table, removing the need for separate tables?

For rows wehere one column has data, and the other doesn't, I'd like to see a null.

[AlexisOlson · Accepted solution]

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

[Steven-conance]

Thanks, good to learn from that experience

[Steven-conance]

Works like a charm in Power Query. The interdependency will be an issue, could maybe be resolved using All / Removefilters and limiting visual interactions. Next thing to think about 🙂 

let
    #"Query1 (2)" = Table.FromColumns(
    {
        List.Numbers(0, 100+1, 10),
        List.Numbers(0, 20+1, 1),
        List.Numbers(0, 100+1, 10),
        List.Numbers(0.5, 1951, 0.01),
        List.Numbers(0.5, 1951, 0.01),
        List.Numbers(0, 501, 0.01),
        List.Numbers(-100, 201,1)
    },
    {"Images - Maximum Selling Price (incl. VAT)", 
    "Images - Minimum Amount", 
    "Images - Minimum Selling Price (incl. VAT)",
    "Minimum Retail Margin",
    "Minimum Special Offer Margin",
    "Setting - Positive Review Score",
    "Shop Breda - Targets"
    }
),
    #"Changed Type" = Table.TransformColumnTypes(#"Query1 (2)",{{"Images - Maximum Selling Price (incl. VAT)", Int64.Type}, {"Images - Minimum Amount", Int64.Type}, {"Images - Minimum Selling Price (incl. VAT)", Int64.Type}, {"Minimum Retail Margin", type number}, {"Minimum Special Offer Margin", type number}, {"Setting - Positive Review Score", type number}, {"Shop Breda - Targets", Int64.Type}})
in
    #"Changed Type"

[AlexisOlson]

I've tried working around the interdependency issue before and did not have a good time. If you want independent slicers, then they really should be independent (in separate tables).

Suppose you want your first column to be 90 and your second column to be 3. You can set up slicers that don't crossfilter each other so that selecting this combination is possible. However, if both of these slicers filter a measure in a card visual, the DAX query that the engine runs to evaluate the measure will be like this

EVALUATE
SUMMARIZECOLUMNS (
    TREATAS ( { 90 }, 'ParameterTable'[Images - Maximum Selling Price (incl. VAT)] ),
    TREATAS ( {  3 }, 'ParameterTable'[Images - Minimum Amount] ),
    "Some Measure", [SomeMeasure]
)

If the measure depends on either of these parameters, then it won't work because the combination of those two filters is an empty ParameterTable. You can't use more than one parameter from ParameterTable at a time if you expect them to be independent.

[Steven-conance]

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 · Accepted solution]

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

[Greg_Deckler]

@Steven-conance It's really hard to say without further context. I'm not sure what you are using these tables for in terms of the business case or what you are actually doing in terms of basing calculations or visuals on these tables.

[AlexisOlson · Accepted solution]

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

[Steven-conance]

I see I was unclear. Sorry @Greg_Deckler 

I'd like to have 1 table, with a separate column for every GENERATESERIES.

So the example from above should result in 1 table with 2 columns

[Greg_Deckler]

@Steven-conance Hmm, maybe:

Table = 
    VAR __Table1 = GENERATESERIES(0, 10000, 10)
    VAR __Table2 = GENERATESERIES(0, 20, 1)
    VAR __Table3 = EXCEPT(__Table2, __Table1)
    VAR __Table3a = EXCEPT(__Table2, __Table3)
    VAR __Table4 = UNION(__Table1, __Table3)
    VAR __Table5 =
        ADDCOLUMNS(
            __Table4,
            "Value1", 
                    VAR __Value = [Value]
                    VAR __Result = MINX(FILTER(__Table3, [Value] = __Value),[Value]) + MINX(FILTER(__Table3a, [Value] = __Value),[Value])
                RETURN
                    __Result
        )
    VAR __Table6 = 
        ADDCOLUMNS(
            __Table5,
            "Value2",
                    VAR __Value = [Value]
                    VAR __Result = IF(__Value IN __Table3, BLANK(), [Value])
                RETURN
                    __Result
        )
    VAR __FinalTable = SELECTCOLUMNS(__Table6, "Value", [Value2], "Value1", [Value1])
RETURN
    __FinalTable

[Greg_Deckler]

@Steven-conance You could do something like this:

Table = UNION( GENERATESERIES(0, 10000, 10), GENERATESERIES(0, 20, 1) )