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 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 comparison
I 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.