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