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