Forum Discussion
Steven-conance
Helper I
3 years agoMultiple 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...
- 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] ) ) - 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
Helper I
3 years agoI 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_Deckler3 years ago
Community Champion
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