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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Pragati11
Super User
Super User

Hi,

 

ALL should work in this case. 

 

Thanks.

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

nbarta
Frequent Visitor

Have you tried looking into KEEPFILTER instead of FILTER? 

Anonymous
Not applicable

@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.

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

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

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.

Anonymous
Not applicable

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors