Forum Discussion

MarlonK's avatar
MarlonK
Icon for Helper I rankHelper I
3 years ago
Solved

Generate 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 ...
  • PaulDBrown's avatar
    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