Hello Community,
assume I have following filters:
VAR __DS0FilterTable =
TREATAS ( { 2023 }, 'ParameterFiscalYear'[ParameterFiscalYear] )
VAR __DS0FilterTable2 =
TREATAS ( { 5 }, 'ParameterFiscalMonth'[ParameterFiscalMonth] )
VAR __DS0FilterTable3 =
TREATAS ( { "R6" }, 'TIME SELECTOR'[TimeSelector] )
The 'TIME SELECTOR' table is defined like this:
TIME SELECTOR =
DATATABLE (
"TimeSelector" , STRING ,
"TimeSelectorInt" , INTEGER ,
"SortOrder", INTEGER,
{
{"R1", 1, 1},
{"R3", 3, 2},
{"R6", 6, 3},
{"R12", 12, 4},
{"FYTD", BLANK(), 5}
}
)
The 'DATE' table's column used in this case are:
'DATE'[FiscalYear] - values 2022, 2023
'DATE'[FiscalMonthInt] - values 1...12
'DATE'[MonthID] - unique ID for every month in the table
Firstly I created a measure that is used for filtering visible months showing up on a chart:
EVALUATE
SUMMARIZECOLUMNS (
'DATE'[FiscalYearMonth],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"IS_MONTH_IN_TIME_SELECTION",
VAR selected_fyear =
SELECTEDVALUE ( 'ParameterFiscalYear'[ParameterFiscalYear] )
VAR selected_fmonth =
SELECTEDVALUE ( 'ParameterFiscalMonth'[ParameterFiscalMonth] )
VAR selected_monthid =
CALCULATE (
SELECTEDVALUE ( 'DATE'[MonthID] ),
ALL ( 'DATE' ),
'DATE'[FiscalYear] = selected_fyear,
'DATE'[FiscalMonthInt] = selected_fmonth
)
VAR selected_time =
SELECTEDVALUE ( 'TIME SELECTOR'[TimeSelectorInt] )
VAR current_monthid =
SELECTEDVALUE ( 'DATE'[MonthID] )
VAR first_month_id_this_fy =
CALCULATE (
MIN ( 'DATE'[MonthID] ),
ALL ( 'DATE' ),
'DATE'[FiscalYear] = selected_fyear
)
VAR result_fytd =
INT ( current_monthid >= first_month_id_this_fy
&& current_monthid <= selected_monthid )
VAR result_rolling =
INT ( current_monthid > selected_monthid - selected_time
&& current_monthid <= selected_monthid )
RETURN
IF (
SELECTEDVALUE ( 'TIME SELECTOR'[TimeSelector] ) = "FYTD",
result_fytd,
result_rolling
)
)
ORDER BY 'DATE'[FiscalYearMonth] DESC
And it works allfine with an IF condition.
Now, I wanted to implement this solution by analogy to generate me a table which could finally be used as a filter in CALCULATE or a calculation item:
EVALUATE
CALCULATETABLE (
CALCULATETABLE(
'DATE',
VAR selected_time =
SELECTEDVALUE ( 'TIME SELECTOR'[TimeSelectorInt] )
VAR selected_fyear =
SELECTEDVALUE ( 'ParameterFiscalYear'[ParameterFiscalYear] )
VAR selected_fmonth =
SELECTEDVALUE ( 'ParameterFiscalMonth'[ParameterFiscalMonth] )
VAR selected_monthid =
CALCULATE (
SELECTEDVALUE ( 'DATE'[MonthID] ),
ALL ( 'DATE' ),
'DATE'[FiscalYear] = selected_fyear,
'DATE'[FiscalMonthInt] = selected_fmonth
)
VAR current_monthid =
SELECTEDVALUE ( 'DATE'[MonthID] )
VAR first_month_id_this_fy =
CALCULATE (
MIN ( 'DATE'[MonthID] ),
ALL ( 'DATE' ),
'DATE'[FiscalYear] = selected_fyear
)
VAR date_filter_fytd =
FILTER (
ALL ( 'DATE' ),
'DATE'[MonthID] >= first_month_id_this_fy
&& 'DATE'[MonthID] <= selected_monthid
)
VAR date_filter_rolling =
FILTER (
ALL ( 'DATE' ),
'DATE'[MonthID] > selected_monthid - selected_time
&& 'DATE'[MonthID] <= selected_monthid
)
RETURN
// date_filter_fytd
IF (
SELECTEDVALUE ( 'TIME SELECTOR'[TimeSelector] ) = "FYTD",
date_filter_fytd,
date_filter_rolling
)
)
,
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3
)
Which returns me an error "A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter" no matter what I put as a conditional check in the IF function (e.g replacing SELECTEDVALUE ( 'TIME SELECTOR'[TimeSelector] ) = "FYTD" with 1=1 returns the same error). When I put the var date_filter_fytd or date_filter_rolling to the RETURN clause then it works allfine.
Any ideas why I cannot use IF to return a table as a result?
Best Regards
Piotr
