Need Help Writing Formula with Multiple Parameters

[Anonymous]

Hello,

I'm relatively new to Power BI and would appreciate some help with a calculation. I have around 12 parameters and have created individual measures for each one. The objective is to create a chart that dispalys an average price of a single field as long as no parameters are filled in. However, when the any of the parameters are filled in I would like for the measure to adjust by calcualting the current price by the parameters that are filled in. I am essentially trying to create one measure that is calcualted using all the parameters and therefore would adjust if/when any parameters are populated. I've tried an IF statement however I am limited on the number of logic test I can record. Can anyone provide some insight how I can write the calculation for this? Any help would greatly appreciated.

-Emmanuel 

[Anonymous]

Hi @Anonymous ,

if you want to have a calculation to select between multiple slicers or what-if parameters you can use the SWICH formula. It performs basically the same function as nested if statements

SWITCH(
    <expression>, // the value that each <value> will be evaluated against

    <value>, // to be matched against <expression>, if <value> = <expression> then perform <result>. This is practically the same as when an IF statement is true
    <result>, // the result if <value> = <expression>
   
    <value2>, 
    <result2>,

     …,

     <else>
)

To evaluate if a slicer is selected you can use the ISFILTERED() formula. 

// in an if statement
IF(
   ISFILTERED([column name of the slicer column]),
     <result>, // if the slicer is selected
     <result> // if the slicer is not selected
)
// in a switch
SWITCH(
     TRUE(),

     ISFILTERED([column name of the first slicer column]),
     <result>, // if the first slicer is selected

     ISFILTERED([column name of the second slicer column]),
     <result>, // if the second slicer is selected
     
     <result> // if no slicer is selected>
)

To add values from a slicer in the <result> part of the formula you can use the SELECTEDVALUE() referencing the column used in each slicer.