simulate a measure by user input value (Value can be as low as 1.0001 and high as 999999.9999)

[deevs]

I have been trying to let a user enter a value between 0.0001 to 999999.9999 and simulate the measure and other charts based on the user input.

Ex : there is measure called Total_sales (287,456,964) in a generuic currency . then the user enters a currecy exchange rate (user_input=99256.2256) .

Totl Sales Adj= Divide(Total_sales ,user_input)

Can someone shed some light how this can be achived?

Thanks,

deevs

[deevs · Accepted solution]

Here We go 🙂

My solution and working like charm 

In my scenario user had to enter exchange rate value range between 0.0001 and 999999.9999 and it was very challenging to deal with even given the what if was working perfectly. Coz if you generate a sequence with 1 million rows the pibx file size will go from 5 MB to 1GB which is a efficient at all.

My way you can avoid this bug which rounds off weirdly as well as it won’t increase your pibx file size.

I created four what if parameters. In your situation you might be able to get it done with two parameters.

 Then I concatenated those four value measures as a text and then converted to a decimal number. I have put a screenshot below. Let me know if it doesn’t work I will share you with a pibx template.

Thanks,

Deevs

This is my

[mahoneypat]

There is not currently an input box like you are describing.  Two potential ideas (neither ideal):

1.  If your user is using the pbix file directly (not published), you could do this with a Power Query parameter (not a What If parameter).  You can create a parameter, set a query to reference it and load it to a one row table, so you could use that value in your calculation.

2. Make two What If parameters - one for the number left of the decimal and the other for the number right of it and then create a measure that adds the two numbers, so you could use that measure in your calculation.  You could have a card with that measure so the user could see the number they created.  Again, not ideal, but gets your functionality w/o millions of rows in the What If.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[deevs]

Hi,

As you can see from the report screenshot below, what I want to achieve is that the  user should be able to enter a value in the exchange rate box below and enter and then based on that, Total PO Value and all the charts should update. and this report will eventaully be published to power BI serivce.

My second question is

At the moment I have created a sequence from 1 to 20000 using what if parameter , then again when you put a value and enter it rounds off in a weird way too.(As you know slider is useless in this scenario as you can't really select a desired value when the sequence is too big)

For an example when entered

1 to 11 it changes it back to 1

12 to 31 it changes it back to  21

And I am super frustrated. Any solution for this too.

[deevs]

Hi mahoneypat,

You are a genius never thought about using two what if parameters to achieve this.

Now I only need help with how to eliminate auto round off error doing behind the scene by power BI when you put a value and enter in the slicer box.

I really can’t use the slider as you can’t select the exact value from say 1- 99999. (As an example 57256 from left and 0.9825 from right)

Would be great if you can help me with that too. I really don’t want a use a drop down as It doesn’t have a search option available.

[v-yingjl]

Hi @deevs ,

You can change the number of places as your need in the column/measure tools bar. If you want to have 4 places of decimal number, you also need to change the increment of the right parameter to 0.0001.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[deevs]

HI yingjl,

I got my two parameters set up correctly and formatted. It’s just that I need some help with this frustrating power BI auto round off error as I mentioned on the earlier response. Please go through my earlier response.

Can someone please help me with this issue ?

[deevs]

I Came up with a solution by myself for this guys. I will share it later . that way you can pretty much handle any range in what if parameters until power bi fix theissue  due to 1002 sampling limit on slicer visual (CRI 115536429)

[deevs · Accepted solution]

Here We go 🙂

My solution and working like charm 

In my scenario user had to enter exchange rate value range between 0.0001 and 999999.9999 and it was very challenging to deal with even given the what if was working perfectly. Coz if you generate a sequence with 1 million rows the pibx file size will go from 5 MB to 1GB which is a efficient at all.

My way you can avoid this bug which rounds off weirdly as well as it won’t increase your pibx file size.

I created four what if parameters. In your situation you might be able to get it done with two parameters.

 Then I concatenated those four value measures as a text and then converted to a decimal number. I have put a screenshot below. Let me know if it doesn’t work I will share you with a pibx template.

Thanks,

Deevs

This is my

[v-yingjl · Accepted solution]

Hi @deevs ,

Thanks for your kindly sharing! You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,

Yingjie Li

[amitchandak]

@deevs , see if  whatif parameter can help

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[deevs]

Hey Amit ,

I have tried what if parameters and it not helping in this scenario at all.

For an example if you generate a series starting from 1.000 with the increment of 0.0001 up to 20000 , you are looking at 99 million rows and the pibx went from 5MB to 1.2GB . Therefore, you can't use what-if parameter in this scenario.
Plus what if parameter is doing some sampling behind the scene and rounding off in a weird way which I am still surprised that no developer in the power Bi team has addressed this issue yet.
is there any way you can store user input into a variable or dummy table and then to use it to simulate a measure? There must be a way to work around I believe.

Thanks,

Deevs