Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter data based on certain measure's value range without losing the other measures in a chart

Dear community members! I'm new to Power BI (worked with it for about three weeks now :-)) so my question (or data) may not be well formulated, apologies for that.   I got a request to show multip...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Measure2 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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.