Forum Discussion

sarjensystems1's avatar
sarjensystems1
Helper III
6 years ago
Solved

Displaying numeric range with supporting table

Hii i have a table with numeric ranges.. with min value and max value...range=5..min value=-5 max value=+5 when i select 5 in the slicer it should display the values of -5 to +5(PIC 2) w...
  • v-frfei-msft's avatar
    6 years ago

    Hi sarjensystems1 ,

     

    I have created a sample for your reference, please check the following steps as below.

     

    1. Unpivot the table like that.

     

    M code for your reference:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilLSUdI1AhGmIMISRBgaAEkIYQgiQNIgbALEZkBsClEeGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [category = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"category", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"category"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"

     

    2. Close and apply, then we can create a measure like that to work it out.

    Measure = 
    VAR slice =
        MAX ( Slicer[Value] )
    VAR ds = - slice
    RETURN
        IF (
            NOT ( ISFILTERED ( Slicer[Value] ) ),
            BLANK (),
            IF (
                MAX ( 'Table'[Value] ) >= ds
                    && MAX ( 'Table'[Value] ) <= slice,
                MAX ( 'Table'[Value] ),
                BLANK ()
            )
        )
    

     

    For more details, please check the pbix as attached.