Forum Discussion
Continued ContributorSELECTEDVALUE returning blanks from "between" slicer
I have 3 [between] slicers that I'm trying to incorporate into a measure.
PBIX file is attached.
Each are coded essentially identically, but the underlying data is % for two of them - call them [LRF] & [Resid] and whole number for the third, [Term]
Avg Resid % based on selection =
AVERAGEX(ALLSELECTED('Residual Parameters'[Residual %]),'Residual Parameters'[Residual %])
When the between slicer is fixed on one number for [Term] the programming for the above measure returns the desired result, i.e., the one number. When the between slicer is fixed on one number for [LRF] and [Resid] the measure returns (BLANK).
In the screenshot below, it shows the measure outputs for [LRF] in the "not working" phase, the [TERM] working as intended, and [Resid] as a work-around.
In this screenshot, it shows the desired input for [RESID] but that the output in the measure returns (Blank)
I've tried using MINX with an IF/THEN statement to work-around, but the measures still return (Blank)
Thanks in advance!
Link to PBIX: https://drive.google.com/file/d/1KXxee9BCMN68eKWgL0PgUEqTlHgklvJm/view?usp=sharing
The workaround to fix this is to use Whole Numbers and then custom format the number by pasting the following into the Format field under the Column Tools menu item:
0.#\%;(0.#\%)
8 Replies
- AlexisOlson
Super User
I think the reason the middle one works while the others fail is that it's an integer type rather than a decimal type. I opened your file and got the others working by switching from decimal type to fixed decimal type.
As a side note, if you create a parameter through the New Parameter button on the ribbon
then you have a Single Value option for the slicer which only has a single box instead of two.
- v-easonf-msft
Community Support
Hi, mrothschild
'Selectedvalue' is used for a single value slicer. If you use 'selectedvalue', you are likely to get a blank value.For obtaining the value in the "between" slicer, you should use 'max' and 'min' to obtain the maximum and minimum values.
If I misunderstood, please feel free to let me know.
Best Regards,
Community Support Team _ Eason
mrothschildHi Anonymous
What I'm trying for is the average between the slider (or manually input) selections in the slicer. Thus, if the report user enters 0.9% and 1.4%, the intended data pull would be 1.15%. If the sliders are collapsed where each are displaying 1.2%, then the intended data pull would be 1.20%. I think MAX and MIN will provide the max and min of the entire column, even if outside the range of the slider/slicer?
Thanks!
- mrothschild
I made a small sample PBIX: https://drive.google.com/open?id=1ctxfnCfdwBMDn0LUiuplkjsbRp_oyyNp
Here, too, if I use MAX/MIN and drag the sliders together, blanks get returned.
- v-easonf-msft
Hi, mrothschild
It is recommended to manually enter the data to avoid accuracy errors
If blanks get returned when you use MAX/MIN and drag the sliders together, it may because the single value doesn't exsit in your fact table list, right?
For example,
value 1.388 (does not exist in your table)so it is naturally not available
value 1.290 (exists in your table)，so the results can be filtered out.
I think the problem is mainly caused by slicer rather than measure .
Please check my attached file.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- mrothschild
I understand your point here.
I guess the bigger question is why - when using a fixed decimal number - can't the slider restrict to the number of decimal points specified. The issue is if I have a number formated #.## and my table has data of . . . .0.89, 0.90, 0.91, 0.92, . . . . then the slicer shouldn't be able to **slide** to 0.9105. How can I have it only slide to either 0.9100 or 0.9200?
- v-easonf-msft
Hi, mrothschild
Sorry,I can't think of other solutions for the time being.
So far, users cannot set the slider limit to the number of decimal points specified.
I would suggest you open a support ticket to get further help from the Microsoft ,they may have better advice or workaround.
https://powerbi.microsoft.com/en-us/support/pro/
Best Regards,
Community Support Team _ Eason
- mrothschild
The workaround to fix this is to use Whole Numbers and then custom format the number by pasting the following into the Format field under the Column Tools menu item:
0.#\%;(0.#\%)
