Forum Discussion
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)
when i select 10 in the slicer it should display the values of -10 to +10..
How to do this??
thank you
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.
2 Replies
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.
Try to put a filter in a calculation like
var _maxval =max(Table[Value]) //In sclicer or Slicer[Value]
return
Calculate([Measure],filter(all(Table[Value]), Table[Value]>=(abs(_maxval )*-1) && Table[Value]<=(abs(_maxval )))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
