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
BrianVT
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
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

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