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
MarlonK
Helper I
3 years agoThank you PaulDBrown these are amazing solutions and I really appreciate the thorough detail and attached PBIX. Thank you for answering my original question and also providing me with a better way!!!