Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
@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:
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)