Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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)
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!