Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 multip...
Need Help
  • Anonymous's avatar
    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

Anonymous's avatar
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's avatar
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Xiaoxin 

        Anonymous 

        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.