Forum Discussion
Filter data based on certain measure's value range without losing the other measures in a chart
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
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
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
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
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:
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.
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