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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pp_pbi
Frequent Visitor

CALCULATETABLE and conditional result

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
0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.