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 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
Hi Xiaoxin,
Anonymous
OK, thanks for the clarification!
Haven't created calculated tables before, so I need to study a bit and then give it another try.
*wish me luck* 🙂
Regards,
Nautsi
Hi again!
I managed to do this!
There are a couple of shortcomings in the solution but we may be able to live with them:
** the DateTime range (or any filter ) & the new table's value field are not interacting with each other => all values from the new table are available in the slicer, regardless of filters selected by the user. I understand this is due to the fact that the new table is not related to the other table in the model (where the filters come from).-> when user selects some measure value range from the slicer, the datetime field does not "reduce" to show only points in time when those values were available/measured
-> this gives user the possiblitiy to select also a range that is not used in the visual/machine he's investigating
-> if the time period is longer than fits to the view, the selected values may be "hidden" beyond the DateTime scroll bar; the user needs to scroll a lot to see the values s/he's interested to use in the data comparisonI need to ask from my requestee if these limitations are acceptable for him; if the end users will understand this behaviour.
If that is ok by him, I'll accept your solution. 🙂
Thank for your great support so far with this, you have really helped me a lot with my first Power BI reporting experiences!Cheers,
Nautsi
I may have other problems to be solved, too (a bit similar to this one), but I'll try myself first with the examples provided by you so far... If I fail, I'll post another question.
Hi Anonymous,
I'm glad to hear this helps. 😊
AFAIK, the slicer is designed as the filter and uses the filter effect to interact with other visuals.According to my experience, most of the customers in the product environment want to use them as selectors instead of filters.
Disconnected slicer that integrates with Dax expressions can be a workaround to ignore some of the limits but they not suitable for all of the scenarios. (it will increase the complexity of data models and add more redundancy structure and visuals: they will affect the performance of reports)So I think you can also consider submitting an idea to add more interaction modes of visuals to improve the humanized of visuals interactions and operations.
How visuals cross-filter each other in a Power BI report
Regards,
Xiaoxin Sheng
Anonymous
Hi Xiaoxin,
I already accepted your solution (although my requestee was not too happy that the DateTime on X-axis was not filtered/'sliced out' accordingly based on the selected value range 😕 ).
But he found some bugs when using the solution with real data that I hadn't noticed in my tests.
-> some of the measurements in the line chart behave as they should, so they are not affected by the new table's SlicerValue selection but for some reason there are measurements which are affected by it.
I tried all kinds of tests with interactions etc but I wasn't able to figure out the reason for the behaviour.I would be truly grateful if you had time to check the solution and let me know if you are able to reproduce the following scenario using the Mock data tests.pbix in Dropbox?
Mock data tests.pbix in Dropbox
First select the visualized measures from the 'Measurements' slicer into the chart for example as follows:
(Measure1, Measure1_AlarmHigh, Measure2, Measure4) - every measure is visualized as they should:
Measure1 values are somewhere around the range 9.00-10.30
Measure1_AlarmHigh limit is 12.00Measure2 values are around the range 5.10-5.90
Measure4 values are around the range 9.90-12.60
Then from the 'Filter Meas values' slicer on the left (= 'SlicerAttribute' in the new table)
select 'Measure4'
And from it's SlicerValue for example the range: 10.55-11.88:Result:
-Measure4 values are filtered according to the range
-Measure1_AlarmHigh with value 12 remains visible (as it should although it's value is above the slicer range)
-Measure2 remains visible (as it should although it's values are below the slicer range)
-But Measure1 is filtered out totally - why?
If I change the measure4 slicer min range to 10.19, Measure2 appears with it’s lowest value of 10.20 at 5.5.2018 15:32:45. <- so it is clearly affected by the range even though it should not.
Are you able to tell what's wrong with the viz, have I done something incorrectly?
Kind regards,
Nautsi
HI Anonymous,
I test a lot with your sample file but not found any changes when you modify the formulas. Finally, I confirm this should more relate to visual interactions. The issue disappeared when you fix the visual interactions.
As you know these tables not really link with relationships. For filters in Dax expressions, they need you to enable visual interactions to get the filter and response for visual operations. (it seems like you turned off the visual interactions so the Dax function cannot get the correct values from slicers)
Regards,
Xiaoxin Sheng
Hi again,
Anonymous
You are absolutely right!
When trying to clean-up the unnecessary(?) interactions I accidentally (or rather due to my misunderstanding) removed the interaction between SlicerAttribute selection and the Line chart.
So _both_ of them (slicer attribute & value) must interact with the visual <- note to self.
I added it back and it works again!
I really appreciate all your efforts in helping me <3.Best regards,
Nautsi
>^..^<
My requestee still needs to do the UAT for this but I hope everything is ok, now that my silly mistake is fixed.