FILTER Function

[Anonymous]

Hi all, 

I am trying to compare columns in my FILTER function but this takes away my ability to add an slicers on the report (not to mention visual, page and report level filters). Please see below. 

Backlog =

IF('Calendar'[Date] <= TODAY(),

CALCULATE([Volume],

FILTER('Case',(('Case'[opened_at] <= 'Calendar'[Date])

&& (('Case'[resolved_at] > 'Calendar'[Date]) || ISBLANK('Case'[resolved_at]))))),

BLANK())

Should I be using a different function? Again, I NEED to be able to compare dates from multiple tables which doesn't seem to be permitted using the standard filtering in the CALCULATE function. I also need to be able to leverage slicers etc. on the report.

Any help would be greatly appreciated!

Thanks, 

sseel

[Samarth_18]

Hi @Anonymous ,

You can add multiple table filter like below:-

Backlog =
IF (
    'Calendar'[Date] <= TODAY (),
    CALCULATE (
        [Volume],
        FILTER (
            'Case',
            (
                ( 'Case'[opened_at] <= 'Calendar'[Date] )
                    && (
                        ( 'Case'[resolved_at] > 'Calendar'[Date] )
                            || ISBLANK ( 'Case'[resolved_at] )
                    )
            )
        ),
        FILTER ( 'table2', 'table2'[column] <= 'calender'[date] ),
        FILTER ( 'table3', 'table3'[column] <= 'calender'[date] )
    ),
    BLANK ()
)

Thanks,

Samarth

[Anonymous]

I tried this, but still unable to get it to work..

Backlog =

IF('Calendar'[Date] <= TODAY(),

CALCULATE([Volume],

FILTER(ALLEXCEPT('Case','Case'[dv_assignment_group]),('Case'[opened_at] <= 'Calendar'[Date])

&& (('Case'[resolved_at] > 'Calendar'[Date]) || ISBLANK('Case'[resolved_at])))),

BLANK())

[v-rzhou-msft]

Hi @Anonymous ,

According to your statement, I think you can try selectvalue or values function to catch value in slicer, then add it in filter. It is not necessary to add so many parentheses in one filter field in your code.

Backlog =
IF (
    'Calendar'[Date] <= TODAY (),
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Case', 'Case'[dv_assignment_group] ),
            'Case'[opened_at] <= 'Calendar'[Date]
                && 'Case'[resolved_at] > 'Calendar'[Date]
                || ISBLANK ( 'Case'[resolved_at] )
        )
    ),
    BLANK ()
)

It is hard for us to find the solution or update your code only based on this code. Please share a sample file with us and show us a screenshot with the result you want. This will make us easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-rzhou-msft, 

All I am looking for is a formula that will allow the end users to dynamically interact with the data. Using the FILTER function blocks the ability to use slicers, visual, page, report level filters etc. Does this make sense?

Thanks, 

sseel

[Anonymous · Accepted solution]

Hi @v-rzhou-msft , 

I figured it out. The key was to add MIN to the calendar dates. 

Backlog2 =

IF (

MIN('Calendar'[Date]) <= TODAY (),

CALCULATE (

[Volume],

FILTER (

ALLEXCEPT ( 'Case', 'Case'[dv_assignment_group],'Employee Lookup'[Mgr_Emp_Name_L5],'Product Lookup'[PE_L6],'Case'[dv_priority] ),

'Case'[opened_at] <= MIN('Calendar'[Date])

&& ('Case'[resolved_at] > MIN('Calendar'[Date])

|| ISBLANK ( 'Case'[resolved_at] ))

)

),

BLANK ()

)

Thanks, 

sseel

[v-rzhou-msft]

Hi @Anonymous ,

As far as I know, use the FILTER function will not block the ability to use slicers, visual, page, report level filters etc. 

Firstly, please make sure that you are creating a measure by your code. In Power BI, only measures could return dynamic results by slicers or filters.

Backlog =
IF (
    'Calendar'[Date] <= TODAY (),
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Case', 'Case'[dv_assignment_group] ),
            'Case'[opened_at] <= 'Calendar'[Date]
                && 'Case'[resolved_at] > 'Calendar'[Date]
                || ISBLANK ( 'Case'[resolved_at] )
        )
    ),
    BLANK ()
)

In this measure, ALLEXCEPT will block the filter from other columns in slicers, visual/page/report level filters except [dv_assignment_group]. You can add columns you want to filter the measure in ALLEXCPET function.

For reference: The (ALL, ALLSELECTED & ALLEXCEPT) Code

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Samarth, 

Correct, but I need the flexibility of the report to be used by multiple users who may require different slicers/filters. This will only solve the issue for one user, not all users who all require different filter selections. Does this make sense?

Thanks, 

sseel