Forum Discussion
MarlonK
Helper I
3 years agoGenerate a Table with pre-defined values in column1, and a range of values in column2
Hello, I'd like to automatically generate a table like this: Tag Value 2 or more 2 2 or more 3 2 or more 4 2 or more 5 2 or more 6 2 or more 7 2 or more 8 ...
- 3 years ago
Try:
n or more = VAR _2OrMoreValues = GENERATESERIES ( 2, 10, 1 ) VAR _Prefix2 = SELECTCOLUMNS ( { "2 or more" }, "Param", [Value] ) VAR _2Table = CROSSJOIN ( _Prefix2, _2OrMoreValues ) VAR _4orMoreValues = GENERATESERIES ( 4, 10, 1 ) VAR _Prefix4 = SELECTCOLUMNS ( { "4 or more" }, "Param", [Value] ) VAR _4Table = CROSSJOIN ( _Prefix4, _4OrMoreValues ) VAR _6OrMoreValues = GENERATESERIES ( 6, 10, 1 ) VAR _Prefix6 = SELECTCOLUMNS ( { "6 or more" }, "Param", [Value] ) VAR _6Table = CROSSJOIN ( _Prefix6, _6OrMoreValues ) VAR _8OrMoreValues = GENERATESERIES ( 8, 10, 1 ) VAR _Prefix8 = SELECTCOLUMNS ( { "8 or more" }, "Param", [Value] ) VAR _8Table = CROSSJOIN ( _Prefix8, _8OrMoreValues ) RETURN UNION ( _2Table, _4Table, _6Table, _8Table )However, for what you seem to need it for, I suggest a different table. Any relationship with the above table wil be many-to-many, which is to be avoided.
Instead, create a table with the threshold for each "n or more", keep it unrelated and then use a measure to return the filtered values:
On or Above threshold = CALCULATE ( [Sum value], FILTER ( 'Product Table', [Sum value] >= SELECTEDVALUE ( 'n or more (unrelated)'[Threshold] ) ) )Sample file attached
PaulDBrown
Community Champion
3 years agoTry:
n or more =
VAR _2OrMoreValues =
GENERATESERIES ( 2, 10, 1 )
VAR _Prefix2 =
SELECTCOLUMNS ( { "2 or more" }, "Param", [Value] )
VAR _2Table =
CROSSJOIN ( _Prefix2, _2OrMoreValues )
VAR _4orMoreValues =
GENERATESERIES ( 4, 10, 1 )
VAR _Prefix4 =
SELECTCOLUMNS ( { "4 or more" }, "Param", [Value] )
VAR _4Table =
CROSSJOIN ( _Prefix4, _4OrMoreValues )
VAR _6OrMoreValues =
GENERATESERIES ( 6, 10, 1 )
VAR _Prefix6 =
SELECTCOLUMNS ( { "6 or more" }, "Param", [Value] )
VAR _6Table =
CROSSJOIN ( _Prefix6, _6OrMoreValues )
VAR _8OrMoreValues =
GENERATESERIES ( 8, 10, 1 )
VAR _Prefix8 =
SELECTCOLUMNS ( { "8 or more" }, "Param", [Value] )
VAR _8Table =
CROSSJOIN ( _Prefix8, _8OrMoreValues )
RETURN
UNION ( _2Table, _4Table, _6Table, _8Table )
However, for what you seem to need it for, I suggest a different table. Any relationship with the above table wil be many-to-many, which is to be avoided.
Instead, create a table with the threshold for each "n or more", keep it unrelated and then use a measure to return the filtered values:
On or Above threshold =
CALCULATE (
[Sum value],
FILTER (
'Product Table',
[Sum value] >= SELECTEDVALUE ( 'n or more (unrelated)'[Threshold] )
)
)
Sample file attached