Forum Discussion

ChrisTof's avatar
ChrisTof
Frequent Visitor
3 years ago
Solved

Filter visual table with a measure not dynamically updated

hi,

I'm using the visual table below with this measures.

 

Number of daysType
1170Red
804Red
511Green
355Orange
346Orange
288Green
274Green
268Red
  
12Red
24Green
126Green

 

 

AverageDays = 
var a=SUMX(ALLSELECTED('Table'),'Table'[Number of days])
var b=COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Number of days]<>BLANK()))
return a/b
Measure = IF(MAX('Table'[Number of days])>'Table'[AverageDays],1,0)

 

I use "Measure" to filter my visual table to display values only greater than the average.

 

 

thanks to Anonymous for the solution but i have a question.

 

When i've filtered the visual, i get this :

Number of daysType
511Green
804Red
1170Red

 

Now, i want to let the users to be able to filter by type using slicer. I mean when they select 'Red', they should get this :

Number of daysType
804Red
1170Red

 

But in fact they get this because when they use the slicer 'type' the measure 'AverageDays' is automatically updated. So the AverageDays was 380 and is now 987 and it displays only values greater than this.

Number of daysType
1170Red

 

I've tried using 'modify interactions' for slicer to set 'none' for the measure 'AverageDays' but still not working.

How can i do so the measure isn't updated when using the slicers ?

Thanks a lot again !

5 Replies

  • Remember that the measure is recomputed each time you interact with the visual. So when you remove Green from the filter context your average value shifts and that then results in only one of the Reds showing. It's working as designed.

    • ChrisTof's avatar
      ChrisTof
      Frequent Visitor

      I agree with you that it's working as designed but it doesn't respond to my use case.

      So how can i do that ?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User

        Use disconnected tables and create measures that can serve as visual filters.

  • ChrisTof's avatar
    ChrisTof
    Frequent Visitor

    I'm a beginner so if you could give me some examples that would be great please.

    Thanks again