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

[Anonymous]

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). 

[Anonymous]

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

[v-shex-msft]

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]

Hi @v-shex-msft  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

[v-shex-msft]

Hi @Anonymous,
I think the basic filter and slicer not suitable for your scenario, you can consider writing a measure expression to use on the visual level filter of your chart to filter records.

Can you please share a pbix file with some dummy data(keep raw table structure, fields) and expected results to help us clarify your requirement and do the test to coding formula on it?

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin 

@v-shex-msft 

Thank you so much for replying!

My original message contains the link to the mock data pbix file in Google Drive.

But if that cannot be accessed, let's try dropbox, too:

pbix in Dropbox 

The chart in question is on the first sheet "Measurement Trends w/ Value Filter"

I tried to illustrate below what the end result should basically look like (the chart in my original mail was probably not clear enough):

Above, the blue lines represent the user selections based on which to limit the data to be visualized.
The red color illustrates what should also remain visible when filtering by value range, but which sadly disappears in my solution attempt.

I hope this helps, and I truly appreciate your support! 

Cheers,

Nautsi

PS: the data is not continuously collected for all measurements so some values may just be data points here and there. That's normal.

[v-shex-msft · Accepted solution]

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

[Anonymous]

Hi Xiaoxin,

@v-shex-msft 

I tried your new measure in the chart but I was not able to achieve the desired result with the value range slicer.... 🤔
(Not sure what to put into the Table[Value] and Table[Attribute] part in your code but I tried almost everything, even duplicating the 'anon longdata' table..)

But my attempt is in the "Mock data testa.pbix" file in Dropbox (sheet 'ORIGINAL of Measurement Trends w/ Value Filter'):

Mock data tests.pbix

Did you manage to make it work with the 'value' range slicer filtering without problems?

(= I just don't yet know how use the tool and DAX properly. )

If this is something that cannot be achieved, I can tell my client that, of course.

But I'm practically open to any creative solution to make this work. 😉

I also spent hours to try to use dual axis approach where left Y-axis would have the actual multiple selected measurements and right Y-axis only one "limiting" measurement and it's value range, but cannot make that work either.

Dual axis solution attemp (on sheet 'TESTING DUAL AXIS Measurement Trends...' in the above attached pbix file):

* All measurements manually created one by one and added to the Left Y-axis.
*Legend  in the chart to be hidden if this solution works.
*  'Measure' slicer box to restrict the visibility of Left Y-axis measures and to control a separate color legend (at the bottom of the page).

*  'Measure Range' slicer to select a limiting measure on the Right Y-axis and the value range slicer. ?? <- goes beyond my abitily....

If this is something that could be achieved, that'd be cool, too. 

Thanks and kind regards,

Nautsi

PS: I was close to make it work if I selected the measurements on Left Y-axis from the 'anon widedata' table (see sheet 'anon widedata TESTING DUAL AXIS...." but then I didn't know how to create a slicer to select those widedata table measurements, hence my "manual measure creation" attempt...

[v-shex-msft]

Hi @Anonymous,

The 'Table' that in my expression is mean a new calculated table which extracts raw table attribute and values and not keep relationships link to the raw table. (it only works as the slicer source to pickup range)

This new table does not directly link to raw tables so these operations on the slicer which used its fields not filter on the raw table.
BTW, I added the processing in measure expression to compare row table row content and slicer selections.

After these operations, the chart graph changes when you choose on slicer with new table fields. (it works on expression levels and not direct effect raw table selections)
Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

@v-shex-msft 

OK, thanks for the clarification!

Haven't created calculated tables before, so I need to study a bit and then give it another try.

*wish me luck* 🙂

Regards,

Nautsi

[Anonymous]

Hi again!

I managed to do this!

There are a couple of shortcomings in the solution but we may be able to live with them:

** the DateTime range (or any filter ) & the new table's value field are not interacting with each other => all values from the new table are available in the slicer, regardless of filters selected by the user.  I understand this is due to the fact that the new table is not related to the other table in the model (where the filters come from).

-> when user selects some measure value range from the slicer, the datetime field does not "reduce" to show only points in time when those values were available/measured
-> this gives user the possiblitiy to select also a range that is not used in the visual/machine he's investigating
-> if the time period is longer than fits to the view, the selected values may be "hidden" beyond the DateTime scroll bar; the user needs to scroll a lot to see the values s/he's interested to use in the data comparison

I need to ask from my requestee if these limitations are acceptable for him; if the end users will understand this behaviour.
If that is ok by him, I'll accept your solution. 🙂

Thank for your great support so far with this, you have really helped me a lot with my first Power BI reporting experiences!

Cheers,

Nautsi

I may have other problems to be solved, too (a bit similar to this one), but I'll try myself first with the examples provided by you so far... If I fail, I'll post another question.

[v-shex-msft]

Hi @Anonymous,

I'm glad to hear this helps. 😊
AFAIK, the slicer is designed as the filter and uses the filter effect to interact with other visuals.

According to my experience, most of the customers in the product environment want to use them as selectors instead of filters.
Disconnected slicer that integrates with Dax expressions can be a workaround to ignore some of the limits but they not suitable for all of the scenarios. (it will increase the complexity of data models and add more redundancy structure and visuals: they will affect the performance of reports)

So I think you can also consider submitting an idea to add more interaction modes of visuals to improve the humanized of visuals interactions and operations.

How visuals cross-filter each other in a Power BI report 

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft 

Hi Xiaoxin,

I already accepted your solution (although my requestee was not too happy that the DateTime on X-axis was not filtered/'sliced out' accordingly based on the selected value range 😕  ).

But he found some bugs when using the solution with real data that I hadn't noticed in my tests.

-> some of the measurements in the line chart behave as they should, so they are not affected by the new table's SlicerValue selection but for some reason there are measurements which are affected by it.

I tried all kinds of tests with interactions etc but I wasn't able to figure out the reason for the behaviour.

I would be truly grateful if you had time to check the solution and let me know if you are able to reproduce the following scenario using the Mock data tests.pbix in Dropbox?

Mock data tests.pbix in Dropbox 

First select the visualized measures from the 'Measurements' slicer into the chart for example as follows:

(Measure1, Measure1_AlarmHigh, Measure2, Measure4) - every measure is visualized as they should:

Measure1 values are somewhere around the range 9.00-10.30
Measure1_AlarmHigh limit is 12.00

Measure2 values are around the range 5.10-5.90

Measure4 values are around the range 9.90-12.60

Then from the 'Filter Meas values' slicer on the left (= 'SlicerAttribute' in the new table)

select 'Measure4'
And from it's SlicerValue for example the range:  10.55-11.88:

Result:

-Measure4 values are filtered according to the range

-Measure1_AlarmHigh with value 12 remains visible (as it should although it's value is above the slicer range)

-Measure2 remains visible (as it should although it's values are below the slicer range)

-But Measure1 is filtered out totally - why?

If I change the measure4 slicer min range to 10.19, Measure2 appears with it’s lowest value of 10.20 at 5.5.2018 15:32:45. <- so it is clearly affected by the range even though it should not.

Are you able to tell what's wrong with the viz,  have I done something incorrectly?

Kind regards,

Nautsi

[v-shex-msft]

HI @Anonymous,

I test a lot with your sample file but not found any changes when you modify the formulas. Finally, I confirm this should more relate to visual interactions. The issue disappeared when you fix the visual interactions.

As you know these tables not really link with relationships.  For filters in Dax expressions, they need you to enable visual interactions to get the filter and response for visual operations. (it seems like you turned off the visual interactions so the Dax function cannot get the correct values from slicers)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi again,

@v-shex-msft 

You are absolutely right!

When trying to clean-up the unnecessary(?) interactions I accidentally (or rather due to my misunderstanding) removed the interaction between SlicerAttribute selection and the Line chart.

So _both_ of them (slicer attribute & value) must interact with the visual <- note to self.

I added it back and it works again!
I really appreciate all your efforts in helping me <3.

Best regards,

Nautsi

>^..^<

My requestee still needs to do the UAT for this but I hope everything is ok,  now that my silly mistake is fixed.

[amitchandak]

@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]))