Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter table if dates range is overlapping other dates range in another table

Greetings.   I have a Table 'C' what contains two columns 'colStart' and 'colEnd'. It's a Date Range.   What I need is a slider that allows to select a dates range and filter 'C' table show...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    It is impossible to create a new table physically (click on "New Table") and make it change with slicer.

    You could use a table visual and make the table visual change with the slicer.

     

    1.Create a calendar date table

    calendar = CALENDAR(MIN(Table1[colStart]),MAX(Table1[colEnd]))

    Edit relationship with "calendar" table and data table based on 'calendar' [Date] to [colStart] and 'calendar' [Date] to [colEnd]

     

    2. create measures

    create measures in the 'calendar' table

    min = MIN('calendar'[Date])

max = MAX('calendar'[Date])

    create measures in the data table

    flag = IF(MAX(Table1[colStart])<=[max]&&MAX(Table1[colEnd])>=[min],1,0)

    3. add 'calendar' [Date] to the slicer, then add 'Table1'[flag] to the Visual Level filter and select "show items when value is 1"

     

     

    Best regards

    Maggie

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
8 years ago

The table can be filtered like this:

 

VAR mnDte = MIN( 'Calendar'[Date] )

VAR mxDte = MAX( 'Calendar'[Date] )

RETURN

CALCULATETABLE(
            DateRanges,
            DateRanges[colEnd] >= mnDte,
            DateRanges[colStart] <= mxDte 
)

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks LivioLanzo but I have created new table with that formula and does not work. It never changes when updating slicer.

It's like MIN( 'Calendar'[Date] ) is returning the lowest date in the table when it should returns the lowest filtered date by slicer. Same with MAX. (my assumptions)

 

 

 

Other suggestion?

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    In other post, I see that v-ljerr-msft said: 

    "Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

     

    So it is not possible to create a calculate column/table can change dynamically with user selections on the report. (...)"

     

    Now I'm totally lost...

     

    How could I achieve the goal then?

    • LivioLanzo's avatar
      LivioLanzo
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Hi Anonymous

       

      in fact the table I have posted is not meant to be used as a physical table. 

      It is a table that is calculated on the fly in your measure as, from what I understood, you want to use it as a filter for another measure

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Sorry LivioLanzo

         

        What I want to filter is 'C' table. Same behaviour like if I select an option in a slicer that it filters the table.