Forum Discussion
sarjensystems1
6 years agoHelper III
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...
- 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.
v-frfei-msft
6 years agoCommunity Support
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.