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

Need to use ISFILTERED to make no selections in slicer equal no filters

I have created a table of distinct values that I'm using as a slicer, and I have a measure that will add one nested measure (which is already counting things) to the count of rows that are equal to anything that matches the selections in the slicer.  The purpose of the slicer is to play with adding certain things based on certain scenarios that the end user would like to test.

 

The measures are actually working fine and displaying the added the counts in my visual, but the problem is, that's only true when I have something selected in the slicer; if I clear the slicer, it's the same as having all of them selected.  I don't want this.  I want an option to have nothing selected, and thus nothing being added in my measure (default count with no scenario).  I know the fix for this is to use ISFILTERED somewhere, but I can't figure out where it should go.  I keep getting an error related to TRUE/FALSE when I throw it in as an extra filter in my CALCUATE functions.

 

Here are my DAX measures and my DAX slicer table:

 

Employee Count (Max FY Period) = CALCULATE(DISTINCTCOUNT('Alpha-Labor'[ID DCPDS]),FILTER('Alpha-Labor','Alpha-Labor'[Fiscal Period]=MAX('Alpha-Labor'[Fiscal Period])))

 

Employee Count (Scenario) = CALCULATE(DISTINCTCOUNT('Alpha-Labor'[ID DCPDS]),FILTER('Alpha-Labor','Alpha-Labor'[Fiscal Period]=MAX('Alpha-Labor'[Fiscal Period])),FILTER('Alpha-Labor','Alpha-Labor'[Series] in values('Series S&E Selection'[Series])))

 

S&E Scenario = CALCULATE([Employee Count (Max FY Period)],'Alpha-Labor'[S&E Series?]="SE")+[Employee Count (Scenario)]

 

Series S&E Selection = CALCULATETABLE(DISTINCT('Alpha-Labor'[Series]),'Alpha-Labor'[S&E Series?]="NON SE")

 

 

The measure in my visual is the "S&E Scenario" and the "Series S&E Selection" is my slicer.  Where should I put the ISFILTERED?

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@BrianVT , not very clear on your formula

You have to try like

if(Isfiltered('Alpha-Labor'[S&E Scenario] ),CALCULATE([Employee Count (Max FY Period)],'Alpha-Labor'[S&E Series?]="SE")+[Employee Count (Scenario)], blank()) 

 

Check my video on same, if that can help -https://www.youtube.com/watch?v=hXg3kRFSGjA



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@BrianVT , not very clear on your formula

You have to try like

if(Isfiltered('Alpha-Labor'[S&E Scenario] ),CALCULATE([Employee Count (Max FY Period)],'Alpha-Labor'[S&E Series?]="SE")+[Employee Count (Scenario)], blank()) 

 

Check my video on same, if that can help -https://www.youtube.com/watch?v=hXg3kRFSGjA



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you!  Yes, I have a lot of other stuff going on in those formulas, but wasn't sure how to simplify it for example.  Per your suggestion, this is what I ended up using:

 

S&E Scenario = if(ISFILTERED('Series S&E Selection'[Series]),CALCULATE([Employee Count (Max FY Period)],'Alpha-Labor'[S&E Series?]="SE")+[Employee Count (Scenario)],CALCULATE([Employee Count (Max FY Period)],'Alpha-Labor'[S&E Series?]="SE"))

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors