Filter data based on certain measure's value range without losing the other measures in a chart

Dear community members!

I'm new to Power BI (worked with it for about three weeks now :-)) so my question (or data) may not be well formulated, apologies for that.

I got a request to show multiple measurement values on a line chart.

In addition, the user should be able to filter the data based on a value range of one of those measurements, and still showing all the other measurements in the line chart as well.

I hope the below picture kind of illustrates what I'm after:

1. First the user selects the measurements/attributes to be visualized on a chart.

2. Selected measurements are shown in another slider, where the user should be able to select one measurement whose certain values to concentrate on.

3. The value range of that one measurement becomes selectable on a slicer

4. The chart should reflect the value range selection (e.g. for Measurement2 range 4,9 - 5) showing all data points of that measurement in the given range

BUT without filtering out the other values from the chart if they fall out of this range.

I have managed to go to number 3, but the other measurement lines disappear if I choose a range that is not applicable to them.

Is there a way to make this happen?
Perhaps some DAX thing? (please bear in mind that I'm a newbie so DAX is not yet familiar to me... ;-))

I tried to load a pbix file with mock-up data to my google drive, but I'm not sure if you can access it:

(https://drive.google.com/file/d/1AQEbsFNqf34ypHpMWMs6nmjtyf2U4lps/view?usp=sharing)

Mock up.pbix

Thanks if someone can help,

Nautsi

PS: Just in case you're wondering...

- The actual data has 108 items to choose from in the slicer (30 Measures, 78 Limits) so I have tried to split the measurements and their upper/lower limits into different tables for clarity (more user friendly slicer selection). I get the nasty Blank()s there but I don't think it is very problematic in this case...

- Customer wants to see the data at the lowest time granularity, so there's this huge scroll bar on the X-axis due to categorical nature of the time data. (Continuous does some strange data binning and will lose the important individual data points).

Need Help

Anonymous
5 years ago
Hi Anonymous,

You can try to use the following measure formula to replace your value field used on the chart, it will replace the selected attribute not matched value parts to blank and not affect other attributes value: (they may reduce the effect on chart graph and axis unit ranges)

Measure = VAR selectValue = VALUES ( Table[Value] ) VAR selectAttr = VALUES ( Table[Attribute] ) VAR currAttr = SELECTEDVALUE ( 'anon longdata'[Attribute] ) VAR currValue = AVERAGE ( 'anon longdata'[value] ) RETURN IF ( currAttr IN selectAttr, IF ( currValue IN selectValue, currValue ), currValue )

Regards,

Xiaoxin Sheng

15 Replies

amitchandak
Super User
5 years ago
Anonymous , Range should be independent

If you want change measure 4 based on measure 2, you need to have something like

new measure 4
var _max = maxx(allselected('Range'),'Range'[Value])
var _min = maxx(allselected('Range'),'Range'[Value])
return
calculate(sumx(filter(values(table[datetime]), [Measure 2] >=_min && [Measure 2] <=_max), [measure 4]))
Anonymous
Not applicable
5 years ago
Hi amitchandak
Thanks for a super fast reply!

I'm not sure I fully understand your DAX *blush*
so unfortunately I don't know how to apply your code to my data...

My main data is in one table ('anon longdata') which is in long format. Here is very simplified excerpt of it (sorry that I forgot to mention it earlier). Basically the 'Attribute' is what the user selects as the measurement name and 'value' is the data that is visualized:

Machine, Model, Number, Date, Time, DateTime, Attribute, value
MachineA 1 10000 13.10.2017 16:56:50 13.10.2017 16:56:50 Measure1 9,8
MachineA 1 10000 13.10.2017 16:59:48 13.10.2017 16:59:48 Measure1 9,7
MachineA 1 10000 13.10.2017 17:07:43 13.10.2017 17:07:43 Measure1 9,8
MachineA 1 10000 13.10.2017 17:12:13 13.10.2017 17:12:13 Measure1 9,6
MachineA 1 10000 13.10.2017 17:13:20 13.10.2017 17:13:20 Measure1 9,4
MachineA 1 10000 13.10.2017 17:21:22 13.10.2017 17:21:22 Measure1 9,8
MachineA 1 10000 16.10.2017 9:36:24 16.10.2017 9:36:24 Measure1 9,7
MachineA 1 10000 16.10.2017 9:56:48 16.10.2017 9:56:48 Measure1 9,8
MachineA 1 10000 16.10.2017 10:03:08 16.10.2017 10:03:08 Measure1 9,6
MachineA 1 10000 16.10.2017 10:11:28 16.10.2017 10:11:28 Measure1 9,4
MachineA 1 10000 13.10.2017 16:56:50 13.10.2017 16:56:50 Measure2 4,5
MachineA 1 10000 13.10.2017 16:59:48 13.10.2017 16:59:48 Measure2 4,9
MachineA 1 10000 13.10.2017 17:07:43 13.10.2017 17:07:43 Measure 2 5
MachineA 1 10000 13.10.2017 17:12:13 13.10.2017 17:12:13 Measure2 4,8
MachineA 1 10000 13.10.2017 17:13:20 13.10.2017 17:13:20 Measure2 4,9
MachineA 1 10000 13.10.2017 17:21:22 13.10.2017 17:21:22 Measure2 4,8
MachineA 1 10000 16.10.2017 9:36:24 16.10.2017 9:36:24 Measure2 4,8
MachineA 1 10000 16.10.2017 9:56:48 16.10.2017 9:56:48 Measure2 3,4
MachineA 1 10000 16.10.2017 10:03:08 16.10.2017 10:03:08 Measure2 3,5
MachineA 1 10000 13.10.2017 16:56:50 13.10.2017 16:56:50 Measure3 1,6
MachineA 1 10000 13.10.2017 16:59:48 13.10.2017 16:59:48 Measure3 1,5
MachineA 1 10000 13.10.2017 17:07:43 13.10.2017 17:07:43 Measure3 1,6
MachineA 1 10000 13.10.2017 17:12:13 13.10.2017 17:12:13 Measure3 1,6
MachineA 1 10000 13.10.2017 17:13:20 13.10.2017 17:13:20 Measure3 1,5
MachineA 1 10000 13.10.2017 17:21:22 13.10.2017 17:21:22 Measure3 1,5
MachineA 1 10000 16.10.2017 9:36:24 16.10.2017 9:36:24 Measure3 1,7
MachineA 1 10000 16.10.2017 9:56:48 16.10.2017 9:56:48 Measure3 2
MachineA 1 10000 16.10.2017 10:03:08 16.10.2017 10:03:08 Measure3 2,1
MachineA 1 10000 16.10.2017 10:11:28 16.10.2017 10:11:28 Measure3 1,5
MachineA 1 10000 13.10.2017 16:56:50 13.10.2017 16:56:50 Measure4 13,2
MachineA 1 10000 13.10.2017 16:59:48 13.10.2017 16:59:48 Measure4 13,6
MachineA 1 10000 13.10.2017 17:07:43 13.10.2017 17:07:43 Measure4 13,6
MachineA 1 10000 13.10.2017 17:12:13 13.10.2017 17:12:13 Measure4 13,5
MachineA 1 10000 13.10.2017 17:13:20 13.10.2017 17:13:20 Measure4 13,6
MachineA 1 10000 13.10.2017 17:21:22 13.10.2017 17:21:22 Measure4 13,4
MachineA 1 10000 16.10.2017 9:36:24 16.10.2017 9:36:24 Measure4 13,3
MachineA 1 10000 16.10.2017 9:56:48 16.10.2017 9:56:48 Measure4 13,9
MachineA 1 10000 16.10.2017 10:03:08 16.10.2017 10:03:08 Measure4 13,8
MachineA 1 10000 16.10.2017 10:11:28 16.10.2017 10:11:28 Measure4 13,5
MachineA 1 10001 16.10.2017 16:30:14 16.10.2017 16:30:14 Measure1 10
MachineA 1 10001 16.10.2017 16:33:24 16.10.2017 16:33:24 Measure1 10
MachineA 1 10002 16.10.2017 16:52:22 16.10.2017 16:52:22 Measure1 9
MachineA 1 10002 16.10.2017 16:54:28 16.10.2017 16:54:28 Measure1 9
MachineB 2 10003 16.10.2017 17:43:40 16.10.2017 17:43:40 Measure1 11
MachineB 2 10003 16.10.2017 17:45:38 16.10.2017 17:45:38 Measure1 10
MachineB 2 10003 16.10.2017 17:47:23 16.10.2017 17:47:23 Measure1 10
...
Then just for convinience's sake I have created another table which contains only those measurement names (from Attribute) that can be used in the measurement name slicers.
(but of course the slicer values could also be taken from the 'Attribute' column of the original data table)

'measurements' -table:
Measure
Measure1
Measure2
Measure3
Measure4

So I'm baffled what to replace the 'Range' with, or how to use 'Measure2', 'Measure4' with my data...
If I was unclear: I don't want to change anything but show all the original values at the points in time when the value range selection for one measurement is valid. (don't know if this is any clearer...).
Tried to depict this with the grey area in the picture.
*Nautsi
- Anonymous
  Not applicable
  5 years ago
  HI Anonymous,
  
  So you mean you want to calculate on the filter data range with dynamic measures that you chose, right?
  If this is a case, I'd like to suggest you use the SWITCH and SELECTEDVALUE functions to achieve these:
  My Favorite DAX Feature: SELECTEDVALUE with SWITCH
  
  Optimizing IF and SWITCH expressions using variables
  
  Regards,
  Xiaoxin Sheng
  - Anonymous
    Not applicable
    5 years ago
    Hi Anonymous and all,
    sorry for the late answer due to Christmas break.
    
    Thanks, Xiaoxin, for your most insightful links, good material to learn and hopefully use in my later try-outs.
    Looks like someone has accepted your solution but I'm afraid my question still remains open.
    
    This is the original requirement I've got: "Compare different parameters on a chart. Enable filtering based on some adujstment parameter, e.g. if "X pressure [bar]" not between 190-210 bar then those results are ignored."
    
    So as an example: I drove a car for 5 hours with various speeds. For this long journey, I want to compare, e.g., the "oil pressure", "fuel consumption" and "speed" but only whenever the "speed" has been between 100-120 km/h (or some other dynamic user-selected range).
    Based on my initial solution (pic above), I would be able to show the measurements selected from the slicers
    (1) and (2), and from slicer (2) the "speed" measurement's value range (3).
    
    The problem is that if I touch the "speed" value range slicer (3) and move it to show values between 100 and 120, it affects the Y-axis scale of the line chart and as a consequence all the other measurements' data in the chart (below or above 100-120) are filtered out. -> I cannot compare the other values against this specially selected speed range anymore.
    
    This is understandably expected behavior, but based on the requirement this should not happen; the "speed" value range (or whatever measurement) selection should only be a special filtering criteria based on which the user wants to limit the data to be compared/visualized.
    
    Is it even possible to apply such a thing in my solution, or is there any other way that this could be achieved?
    
    Thanks if someone can help,
    Nautsi

Forum Discussion

Filter data based on certain measure's value range without losing the other measures in a chart

15 Replies

Related content

Measure

DAX Measure

Path Measure

% of total measure

Count Measure