Forum Discussion
Multiple GENERATESERIES columns in 1 table
- 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
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"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.