Passing between parameters to a measure using Whatif Analysis

[Krishna_Mysore]

Hello Folks

I'm trying to build a What if Analysis using Parameters . I know how to pass the parameter values if a single value is selected, however my need is to use a range ieBetween Values with parameters.  Can someone help me as to how I would reference the parameters range in my measure. I tried using Min and Max value for the range selected but I get an error.

The below measure works if a single value is passed through with the parameter 

Avg Selling Price Filtered = if(hasonevalue(View[Cal ID]),SWITCH([Calculation ID],1,IF([Avg Selling Price (Billed) Monthly]>=Parameter[Parameter Value],1,0),2,IF([Avg Selling Price (Billed) YTD]>=Parameter[Parameter Value],1,0)))

Avg Selling Price Filtered Range = IF(AND([Avg Selling Price (Billed) Monthly]>=MIN(Parameter[Parameter Value]),[Avg Selling Price (Billed) Monthly]<=MAX([Avg Selling Price (Billed) Monthly])),[Avg Selling Price (Billed) Monthly],0)

Appreciate if you can guide to fix this measure.

Thanks

[v-xjiin-msft · Accepted solution]

Hi @Krishna_Mysore,

As I know, currently it is not supported to use multi valued parameter in Power BI desktop. It also means that we can only refer a single value parameter in measure, not the parameter range.

So to achieve your requirement, I think we need do some workarounds. Like define two parameters, one used for the MIN value and one is for the MAX value. Then use two parameters in measure.

Or, you can just use the between mode Slicer to filter data. Please refer: Use the numeric range slicer in Power BI Desktop 

Thanks,
Xi Jin.

[v-xjiin-msft · Accepted solution]

Hi @Krishna_Mysore,

As I know, currently it is not supported to use multi valued parameter in Power BI desktop. It also means that we can only refer a single value parameter in measure, not the parameter range.

So to achieve your requirement, I think we need do some workarounds. Like define two parameters, one used for the MIN value and one is for the MAX value. Then use two parameters in measure.

Or, you can just use the between mode Slicer to filter data. Please refer: Use the numeric range slicer in Power BI Desktop 

Thanks,
Xi Jin.

[MFelix]

Hi @Krishna_Mysore,

Change your measure to

Avg Selling Price Filtered Range =
CALCULATE ([Avg Selling Price (Billed) Monthly], 
Avg Selling Price (Billed) Monthly]>=MIN(Parameter[Parameter Value]) &&[Avg Selling Price (Billed) Monthly]<=MAX([Avg Selling Price (Billed) Monthly]
)

[Krishna_Mysore]

Hi Felix

I tried adopting your measure but didnt resolve. Below is the screen dump of the error.

[MFelix]

Hi @Krishna_Mysore,

Sorry for not responding earlier, but add some complications with my computer and lost track of this post. 

What is the name of your parameter table and your column in that table?

You should be abble to use that to make this. If you are using a what if analysis it's created a table so it can be used as any other table.

Regards,

MFelix

[Krishna_Mysore]

Hi @MFelix

I have got the default Parameter table name as "Parameter" and I'm performing the "what if Analysis"  on a measure  and not on a column.  

Thanks

[MFelix]

Hi @Krishna_Mysore,

You want to use the what if analysis but it's not only with one value, you want to have a range so in this case the measure that is created automatically won't solve your issue, you need to change your slicer of the what if analysis to a between and then make the formula as I mention because that will get the top and lowest value on your waht if.

Regards,

MFelix

[Krishna_Mysore]

@MFelix

I have

1 Set my paratmeters slicer to between range.

2 Applied your suggested formula.

I endup with the above error when I try to incorporate the MIN and MAX funtion agasinst the parameter table.

[MFelix]

Hi @Krishna_Mysore,

You have the wrong column name in your formula, in the part where you have:

MIN(Parameter[Parameter Value])

MAX(Parameter[Parameter Value])

You should put the table parameter name and the corresponding column looking at your print screen I assume should be something like this:

MIN(Parameter[Parameter])

MAX(Parameter[Parameter])

Not sure if the table name is correct because I can only see the column name choosen in your slicer. To what I can read on the first post you made the Parameter Value is the calculated measure when you have the what if analysis for a single value.

As a good practice in DAX you should do the following notations when refering to an object:

Column = Table[Column]

Measure =  [Measure]

This means if you use a column in your calculations you should always refer the table name making it context, if it's a measure you should only use the measure name.

Regards,

MFelix

[Krishna_Mysore]

Hi @MFelix

I didnt realise that I was not referrring to the correct column name in my measure. Nevertheless I made the correction now but still it doesnt work. 

Below are the images of my parameter table and the measure with error.

[MFelix · Accepted solution]

Hi @Krishna_Mysore,

When you use a measure since it's based on context it returns an error. 

Try to change your measure to something like this:

Avg Selling Price Filtered Range =
VAR av_price = [Avg Selling Price (Billed) Monthly]
RETURN
    CALCULATE (
        [Avg Selling Price (Billed) Monthly],
        FILTER (
            Table,
            av_price >= MIN ( Parameter[Parameter] )
                && av_price <= MAX ( Parameter[Parameter] )
        )
    )

Change Table by the name of the table that as your data.

Regards,

MFelix

[Krishna_Mysore]

That worked.  Thanks a lot Felix