March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
87 | |
87 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |