Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
@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
@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"))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |