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
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
Icon for Super User rankSuper 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