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 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
Hi Xiaoxin,
Anonymous
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'):
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...