Forum Discussion

salimgs's avatar
salimgs
Frequent Visitor
9 years ago

Accessing Slicer filtered values through columns

I have a Dates table that lists daily dates in a range. I put a slicer on it.

I have a second table (lets call it Items) that is not related to Dates table but I need to have access to minimum and maximum filtered range of Dates selected by slicer.

If I define a measure =MAX(Dates[Date]) the value changes as the slicer changes.

But if I define a column in Items table, MAX(Dates[Date]) expression ignores slicer's range.

 

How can I access Slicer filtered values through columns?

 

PS: In my scenario I cannot set up a relationship between Dates and Items tables because I have multiple date columns in Items that cannot have a one-to-many relationship with Dates.

Need Help

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    HI salimgs

     

    Why does it have to be a column?  I wonder if you can achieve the effect you are after with a measure after all?

    • salimgs's avatar
      salimgs
      Frequent Visitor
      9 years ago

      Thanks Phil_Seamark for your response.


      I can achive the effect with measure but I need it in column because I want to create a "flag" column to put it in visual filter pane.

       

      Here is the scenario:

      Date table has just one [date] field holding all posible dates. I have a slicer on this.

      Items table has [Title], [forecast start date] [forecast finish date] [actual start date] [forecast start date]

      Now I want to show a table with just the rows that at least one of its four date columns are in the sliced date range.

       

      I can make a measure like this and it works:

      Flag = CALCULATE(HASONEVALUE(Items),FILTER(Items, Items[forecast start date]>=(MIN(Date[date] && Items[forecast start date]<=MAX(Date[date]) || (Items, Items[forecast finish date]>=MIN(Date[date] && Items[forecast finish date]<=MAX(Date[date]))

      But I cannot use this measure to visually filter my rows in Items (or at least don't know how to).

       

      Any idea?

      • v-caliao-msft's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        salimgs,

         

        You could create a measure like:

        Measure = IF(MAX('Item'[forecast start date])>='Table'[MinDate]&&MAX('Item'[forecast start date])<='Table'[MaxDate],1,0)+IF(MAX('Item'[forecast finish date])>='Table'[MinDate]&&MAX('Item'[forecast finish date])<='Table'[MaxDate],1,0)+IF(MAX('Item'[actual start date])>='Table'[MinDate]&&MAX('Item'[actual start date])<='Table'[MaxDate],1,0)+IF(MAX('Item'[autual finish date])>='Table'[MinDate]&&MAX('Item'[autual finish date])<='Table'[MaxDate],1,0)

        Then add this measure to your visual filter

         

        Regards,

        Charlie Liao