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
mrothschild
Continued Contributor
Continued Contributor

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

 

SELECTEDVALUE.PNG

 

In this screenshot, it shows the desired input for [RESID] but that the output in the measure returns (Blank)

SELECTEDVALUE1.PNG

 

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

 

 

 

 

 

1 ACCEPTED SOLUTION
mrothschild
Continued Contributor
Continued Contributor

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.#\%)

View solution in original post

8 REPLIES 8
mrothschild
Continued Contributor
Continued Contributor

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.#\%)

v-easonf-msft
Community Support
Community Support

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

34.png

value 1.290 (exists in your table),so the results can be filtered out.

35.png

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.

 

 

 

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?

 

 

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

 

 

 

v-easonf-msft
Community Support
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

Hi @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!

 

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.

 

AlexisOlson
Super User
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

AlexisOlson_1-1617982345700.png

 

then you have a Single Value option for the slicer which only has a single box instead of two.

 

AlexisOlson_0-1617982254720.png

 

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.