cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Geeco1 Helper I
Helper I

How to have a measure with filters ignore a slicer

I have the following measure with existing filters....

 
Beg Active Employee =
    CALCULATE(
        COUNTROWS('Employee Data'),
        FILTER ('Employee Data', 'Employee Data'[EffectiveDate] <= [MinDate]
                 && 'Employee Data'[NewEndDate] >= [MinDate])
            ) + 0
 
This is one of 3 measures that I have that are very similar.  The 3 measures are then used in a calculation in another measure.  I also have a slicer on the main page.  I need the measure above to IGNORE the slicer selections.  I have tried ALL, ALLExcept, etc with no luck.  Any ideas??
8 REPLIES 8
Super User II
Super User II

Re: How to have a measure with filters ignore a slicer

@Geeco1 ALL or REMOVEFILTERS should work. Not sure what field you are using in slicer. Can you please share sample data along with expected result to reproduce the scenario.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
nbarta
Frequent Visitor

Re: How to have a measure with filters ignore a slicer

Have you tried looking into KEEPFILTER instead of FILTER? 

Pragati11 Resident Rockstar
Resident Rockstar

Re: How to have a measure with filters ignore a slicer

Hi,

 

ALL should work in this case. 

 

Thanks.

Geeco1 Helper I
Helper I

Re: How to have a measure with filters ignore a slicer

Using ALL gets close but not quite what I need.  All of the data comes from a single table.  The measures that I am using are

         # of employees at the Beginning of a period

         # of employees at the End of a period

         # of Employees have terminated.

The page has 3 slicers (Type of Termination, Location, & Position)  I want the measures that calculates the Beg and End employee count to ignore the Type of Termination slicer, but still use the Location and Position slicers.  The Terminated employee measure should use all slicers.

 

When I used ALL in the Beginning and End measures, it would then also not let me use the Location or Position slicers.

Anonymous
Not applicable

Re: How to have a measure with filters ignore a slicer

Beg Active Employee =
    CALCULATE(
        COUNTROWS('Employee Data'),
        FILTER (All('Employee Data'), 'Employee Data'[EffectiveDate] <= [MinDate]
                 && 'Employee Data'[NewEndDate] >= [MinDate])
            ) + 0
 
Use above measure.
 
Thanks,
Pravin
Geeco1 Helper I
Helper I

Re: How to have a measure with filters ignore a slicer

I tried that one... however it then ignores ALL slicers.  I only want the measure to ignore 1 slicer of the several on my page.

UlfBergqvist Responsive Resident
Responsive Resident

Re: How to have a measure with filters ignore a slicer

You could add additional parameters to calculate to ignore filters on specific columns:

 

Beg Active Employee =
    CALCULATE(
        COUNTROWS('Employee Data'),
        KEEPFILTERS('Employee Data'[EffectiveDate] <= [MinDate] && 'Employee Data'[NewEndDate] >= [MinDate]),
        ALL('Employee Data'[ColumnToIgnore1]),
        ALL('Employee Data'[ColumnToIgnore2])
            ) + 0
Anonymous
Not applicable

Re: How to have a measure with filters ignore a slicer

You can use allexcept function or you can try keepfilter function. It will resolve your issue.

Thanks
Pravin

If it resolves your problem mark it as a solution and give Kudos.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.