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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.