Forum Discussion

Nicolas_H's avatar
Nicolas_H
Frequent Visitor
3 years ago
Solved

Filtering data between validity periods from an input parameter

Hello,   I need to filter some data with a refence date (which is entered by the user) using 2 dates (start date & end date) that define a validity period.   My table contains :   I want ...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Nicolas_H,

     

    You could use SWITCH to determine which table is applicable:

     

    Visual Filter = 
VAR vSelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
    SWITCH (
        TRUE,
        ISINSCOPE ( FactTable[Start Date] ),
            SUMX (
                FactTable,
                IF (
                    vSelectedDate > FactTable[Start Date]
                        && vSelectedDate < FactTable[End Date],
                    1
                )
            ),
        ISINSCOPE ( FactTable2[Start Date] ),
            SUMX (
                FactTable2,
                IF (
                    vSelectedDate > FactTable2[Start Date]
                        && vSelectedDate < FactTable2[End Date],
                    1
                )
            )
    )
RETURN
    vResult
DataInsights's avatar
DataInsights
Super User
3 years ago

Nicolas_H,

 

Create the measure below and use it as a visual filter ("is greater than 0"). The Calendar table does not have a relationship with the fact table. The date slicer uses the Calendar table.

 

Visual Filter = 
VAR vSelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
    SUMX (
        FactTable,
        IF ( vSelectedDate > FactTable[Start Date] && vSelectedDate < FactTable[End Date], 1 )
    )
RETURN
    vResult

 

Nicolas_H's avatar
Nicolas_H
Frequent Visitor
3 years ago

Thanks for your answer, it's a great step for me 🙂

So if i understant well, i have to create a measure to filter each table with validity periods ?

And is there a mean to drive all these filters with only one "general filter" ?

 

Because if i've got 20 tables with validity periods, when i want to cross all the data in one report table, i 'd prefer use 1 filter rather than 20...

 

Thanks

Nicolas

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    Nicolas_H,

     

    You could use SWITCH to determine which table is applicable:

     

    Visual Filter = 
VAR vSelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
    SWITCH (
        TRUE,
        ISINSCOPE ( FactTable[Start Date] ),
            SUMX (
                FactTable,
                IF (
                    vSelectedDate > FactTable[Start Date]
                        && vSelectedDate < FactTable[End Date],
                    1
                )
            ),
        ISINSCOPE ( FactTable2[Start Date] ),
            SUMX (
                FactTable2,
                IF (
                    vSelectedDate > FactTable2[Start Date]
                        && vSelectedDate < FactTable2[End Date],
                    1
                )
            )
    )
RETURN
    vResult
    • Nicolas_H's avatar
      Nicolas_H
      Frequent Visitor
      3 years ago

      I think i can do with that

      Thanks for the help