Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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] )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Felix
I tried adopting your measure but didnt resolve. Below is the screen dump of the error.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThat worked. Thanks a lot Felix
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |