Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
C-Jac
Helper I
Helper I

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? 

 

2 REPLIES 2
amitchandak
Super User
Super User

@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)

I tried this but it gives me following error: 

C-Jac_0-1623072080528.png

 

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)

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors