What-if parameter is rounding up the value but the value should round down in PowerBI

[Uthraa92]

Hi all,

I have a problem with What-if Parameter, as the single value input is not working properly for huge amount value, I used the Slicer as "Between" and used the range for the value GENERATESERIES(0, 1000000000, 50) which increments 50. As I need the single input to be specified, I just considered the Min range value and hiding the Max range. But when the user inputs the min range value as 2232, I need to consider MIN(Parameter Value) which is rounding up and considering 2250.However, I need the value to be rounded down as 2200. For example as below:

Need a quick way to resolve this. Any Idea ?

Thanks.

[Anonymous]

Hi, @Uthraa92 

Thanks for the replies from vojtechsima and Singamshetty994. When you use Between Slicer, it filters out all the values in that range, and doesn't include values that aren't in that range, so it's understandable that it can't be rounded down. I think you can do the following to achieve your needs.

Way 1: Use this dax to get the round down value.

Round down = MIN(Parameter[Parameter]) - 50

Way 2: Set the offset, and set as mininum to show the value in Card visual

Delete 50 = IF(Parameter[Parameter]>0,Parameter[Parameter]-50,0)

A few caveats when using What-if parameters:
Use parameters to visualize variables - Power BI | Microsoft Learn

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Uthraa92]

@Anonymous - Thanks for the detailed case and apologies for not responding sooner. 

I had tried first option before, the main problem is this will minus 50 even for the cases like 2250. I understand that if my value is 2262 it is considering as 2250. But if my input is 2250, I must go with 2250, but with this logic, it will consider the input as 2200 which is not correct. Only when the input is 2230 or 2205, I need it as - 50. So both the cases doesnt work in this logic. Anyways really appreciate your suggestions. Thanks!

[Anonymous]

Hi, @Uthraa92 

If the increment is set to 50, it is not possible to capture the input value (e.g. capture 2262), and even with DAX, it is not possible to round down and multiply the value by 50 at the same time. If you set the increment to 1, the table data model is too large and severely impacts query performance, so I don't think the current version of PBI Desktop will fulfill your needs. You can submit an idea for here and vote it, the product team will take note of such a need.

Related Link: New tab (powerbi.com)

Best Regards,
Yang
Community Support Team

[vojtechsima]

Hello, @Uthraa92 ,

I recommend making the range smaller: Parameter = GENERATESERIES(0, 10000, 50) with this setup, I got correct behaviour. you shouldn'T prolly need hundred thousand / 50 rows if you need to calculate precision of 50.

[Uthraa92]

Hi @vojtechsima,

I have the amount value as 53520302 as input, so I had to give the max range available and since increment by 1 takes lot of memory space, I am limiting to 50.

[vojtechsima]

@Uthraa92 ,

do you need the slider visual? Because I think you just input the value right?, you can just create table in Power Query that way it won't take memory when working with the report. And then have measure that checks the current value.

[Uthraa92]

@vojtechsima - Are you asking me to create a table with values from 1 to 1000000000 with increment by 1. and then consider this column as filter for user input ?

[vojtechsima]

@Uthraa92  I mean kinda 😄 

EDIT: Nah, don't do that, the table is enormous then. If you use Between slicer. You can just you MIN(Paramter) and MAX(Parameter) instead of SELECTEDVALUE.

[Singamshetty994]

Hi @Uthraa92 

You can create a custom DAX function to round down the parameter value to the nearest 50.

RoundDownTo50 =
VAR ParameterValue = SELECTEDVALUE(WhatIfParameter[Value])
RETURN
FLOOR(ParameterValue, 50)

Then, you can use this function in your measures to filter data based on the rounded-down value

FilteredMeasure =
CALCULATE(
SUM(YourTable[MeasureColumn]),
YourTable[ValueColumn] >= RoundDownTo50()
)

[Uthraa92]

@Singamshetty994 Thanks for your input.

SELECTEDVALUE doesnt work if I have slicer settings as between and if Im choosing the MIN(ParameterInput). I too tried FLOOR(ParameterValue, 50) earlier but it is not working as it is directly considering the ParameterValue as 2250 instead of 2202 for the FLOOR function to work. Am I doing anything wrong here ?

[Singamshetty994]

Hi @Uthraa92 
You're right, SELECTEDVALUE might not work as expected when using a slicer with a "Between" filter and a minimum value. The FLOOR function should work correctly if the ParameterValue is being passed correctly to the function.

Here's an alternative approach using DAX to filter the data based on the rounded-down parameter value

FilteredMeasure =
CALCULATE(
SUM(YourTable[MeasureColumn]),
FILTER(
ALL(YourTable),
YourTable[ValueColumn] >= ROUNDDOWN(SELECTEDVALUE(WhatIfParameter[Value]), -2)
)
)

It directly filters the data based on the rounded-down parameter value, avoiding potential issues with the FLOOR function.

If you continue to face issues, please provide more details about your specific data model, DAX measures, and the exact behavior you're observing.