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:

TagValue
2 or more2
2 or more3
2 or more4
2 or more5
2 or more6
2 or more7
2 or more8
2 or more9
2 or more10
4 or more4
4 or more5
4 or more6
4 or more7
4 or more8
4 or more9
4 or more10
6 or more6
6 or more7
6 or more8
6 or more9
6 or more10
8 or more8
8 or more9
8 or more10

 

So:

Tag = "2 or more" generate rows with range 2-10

Tag = "4 or more" generate rows with range 4-10

Tag = "6 or more" generate rows with range 6-10

Tag = "8 or more" generate rows with range 8-10

 

Ultimately I want to link this table to my main table on the Value column, and use it to create a >= filter in a visual that is inclusive, so that the user can chose "2 or more" and get values >= 2, select "4 or more" and get values >=4, and so on.

 

Open to other suggestions to accomplish this as well if you think there is a better way, but would still love to know if this can be done the way I'm describing above. 


Thanks!

-Marlon

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    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's avatar
    MarlonK
    Icon for Helper I rankHelper I
    3 years ago

    Thank 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!!!