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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Decal
Helper I
Helper I

Slicer Not Affecting Measure

I've read a lot of articles out there on this, and just can't seem to find a solution.

 

This is what I'm doing.  I am calculating an employee attrition rate.  My users want to be able to slice it be time type (Full time, part time, per diem) and by termination type (voluntary, involuntary).  I have these as slicers on my page.

 

The calculation is terminations (count is affected by filters above) divided by the average of (the beginning of the period [e.g., Nov 1] and the end of the period [e.g., Nov 30]).  

 

The begining of the period (BoP) is affected by the filter, so I had to add a DAX clause to ignore the filter - has to do with how the data is store; the same is true for the end of the period (EoP).  I finally got those to be appropriately unaffected by the slicer filter.  When I drop in my terminations measure, it does not change with the slicer filter.  When I put that same measure in a different visual on the same page, it filters just fine.  So, I believe the problem lies in how I got my BoP and EoP to ignore the filter.

 

Here is the language for the BoP:

BoP Employees =
VAR _bop = MIN(dimDate[Date])
VAR _eop = MAX(dimDate[Date])
VAR _bopemps =
CALCULATE(
    DISTINCTCOUNT(dimWorker[EEID]),
    FILTER(dimWorker,
    dimWorker[Hire Date]<=_bop &&
    (dimWorker[Term Date]>=_bop || ISBLANK(dimWorker[Term Date]))
    )
)
RETURN _bopemps
 
I then added another measure to ignore the termination type:
BoP Employees (Ignore Filter) =
CALCULATE([BoP Employees],
ALL(dimWorker[CF EE Termination Voluntary/Involuntary])
)
 
The EoP looks the same.
 
Is there some DAX language I can put in my Terminations measure to have it use those filters?
 
Here is the current measure:
Terminations (Dynamic) =
VAR _bop = MIN(dimDate[Date])
VAR _eop = MAX(dimDate[Date])
VAR _terms =
CALCULATE(
    DISTINCTCOUNT(dimWorker[EEID]),
    FILTER(dimWorker,
    (dimWorker[Term Date]>=_bop && dimWorker[Term Date]<=_eop)
    )
)
RETURN _terms
 
I tried to create this measure to use the filters, but it doesn't work:
Terminations (All Selected) =
CALCULATE([Terminations (Dynamic)],
ALLSELECTED(dimWorker[CF EE Termination Voluntary/Involuntary],(dimWorker[Time Type]))
)
2 REPLIES 2
Sahir_Maharaj
Super User
Super User

Hello @Decal,

 

Can you please try this:

Terminations (All Selected) =
VAR _bop = MIN(dimDate[Date])
VAR _eop = MAX(dimDate[Date])
RETURN
CALCULATE(
    [Terminations (Dynamic)],
    ALLSELECTED(dimDate),
    ALLSELECTED(dimWorker[CF EE Termination Voluntary/Involuntary]),
    ALLSELECTED(dimWorker[Time Type])
)

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

I appreciate the attempt, but unfortunately that did not work.   The number should have moved from where it is a 22 for my sample month to 19.  Instead it moved up to to 74.  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.