Forum Discussion

C-Jac's avatar
C-Jac
Helper I
5 years ago

SWITCH between filters

I have a date table with some custom periods, where if the date is included in the period it is marked with 1.

 

What I need is a filter where I can select which of these custom periods I would like to see, and as far as I can see, I should use switch. 

 

Table 1: Transacitons

TransactionDateID  | TransactionAmount

 

Table 2: TransactionDates

TransactionDateID | TransactionDate | CustomPeriod1 | CustomPeriod2 | CustomPeriod3

 

So the slicer should look like this: 

Period: 

#CustomPeriod1

#CustomPeriod2

#CustomPeriod3

 

And if number 1 is selected, the filter should apply that only rows where the CustomPeriod1 = 1 should be included and so on. 

 

Can you help me? 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    C-Jac , You need to have a table with three rows

    CustomPeriod1 

    CustomPeriod2 

    CustomPeriod3

     

    Now based on the selected input 

     

    measure =

    Var _sel = Switch(selectedValue(Period[period]) ,

    "CustomPeriod1", Filter(TransactionDates,TransactionDates[CustomPeriod1]=1),

    "CustomPeriod2", Filter(TransactionDates,TransactionDates[CustomPeriod2]=1),

    "CustomPeriod3", Filter(TransactionDates,TransactionDates[CustomPeriod3]=1)

    )

    return 

    calculate(sum(Transaction[TransactionAmount]), _sel)

    • C-Jac's avatar
      C-Jac
      Helper I
      5 years ago

      I tried this but it gives me following error: 

       

      Repayments By Period:=

      VAR _sel = SWITCH(SELECTEDVALUE(ShowPeriodsTable[Period]),
      "1 Year" , FILTER(_TransactionDateCalendar, _TransactionDateCalendar[ThisQuarterAndNext3] = 1),
      "2 Years" , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[ThisQuarterAndNext7] = 1),
      "3 Years", FILTER(_TransactionDateCalendar , _TransactionDateCalendar[ThisQuarterAndNext11] = 1),
      "4 Years", FILTER(_TransactionDateCalendar, _TransactionDateCalendar[ThisQuarterAndNext15] = 1)
      )

      RETURN
      CALCULATE(SUM(Fact_Transaction[Transaction Amount]), _sel)