Forum Discussion

mikhailry's avatar
mikhailry
Frequent Visitor
5 years ago

Measure slicer

Hi everyone,

 

I was wondering if you guys can help with tips and tricks on how to slice the data based on an aggregated column. 

 

I have raw data stored in the Sessions table like this: 

Sessions table

Table: Sessions

 

Different articles are viewed on several websites. I need to aggregate the data to see the number of views for each article with the ability to filter by website and date. Everything looks good in the table, where Views = sum( 'Sessions'[views]).

What I want to do is to make the 'Views' slicer dynamic based on other filters selection. 

 

When no filters are selected: Views slicer should show a range between 111 and 220:

View: no filters selected

When the 'Website1' filter is selected, the Views slicer should show a range between 100 and 200, and if I change the range on the slicer to be between 100 and 150, the table should show only article 1. 

View: website1 filter selected

 

I was thinking of creating a measure to calculate the total amount of views, but I can't use measure in the slicer. Any word of advice will be helpful on how to approach it in the most efficient way.

 

Thank you

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    mikhailry , assuming view is  measure and you want to control it by slicer viewslicer

     

    Measure  =

    var _min = minx(allselected(Viewsslicer), Viewsslicer[values])

    var _max = maxx(allselected(Viewsslicer), Viewsslicer[values])

    return 

    Sumx(values(Table[Article]), if([Views] >=_min && [Views] <=Max , [View], blank()))

    • mikhailry's avatar
      mikhailry
      Frequent Visitor
      5 years ago

      Hey amitchandak ,
      Thanks for your response. 


      I'm not sure that I'm following your solution. Let me try to elaborate on the problem. 
      Per my understanding, I need to create the following:
      1. Measure to calculate Total Views in the table. I guess it should be something like that:

      views_m = CALCULATE(sum(sessions[Views]), ALLSELECTED(sessions[Website]))
       
      2. Column or table to feed the slicer (can't assign a measure to the slicer). The max and min values in the slicer should be dynamic, based on selection in other filters, for example, if 'website 1' filter is checked, the slicer's min value should be 100 and a max value = 200.  
      And if I change the min value in the slicer to be 150, I shoud see only one row in the table - article 2 | 200.
       
      Is that a right logic?
      Thanks