How to create conditional variable with dynamic values?

[iadbmd]

Context: I have a table which is connected in Power BI through a query. I am creating a dashboard for users which test scenarios by adjusting a number of parameters (50+) using dropdowns to set values from 0-2 in 0.1 increments. I need to test the table to set a new variable to one of these dropdowns which are dynamically set and changed. 

Sample: There are thousands of IDs. Type can take 1 of 2 values: green or red. Phase can take 1 of 2 values: Active or Inactive.

I have a table setup as follows:

ID	Type	Phase	Value
1	Green	Active	G_Active
2	Blue	Active	B_Active
3	Green	Inactive	G_Inactive

Task: Create a new variable Value which can take 1 of 4 values: G_Active, G_Inactive, B_Active, B_Inactive. These 4 values are dynamic and set/adjusted by users using slicers.

Value is assigned as follows: If(Type=Green & Phase=Active, Value = G_Active) Else(...)

How can this be created in Power BI? In total, I need to do this 10 times for 10 different variables, each testing different variables and setting to additional dynamic values.

[Rupak_bi]

HI @iadbmd ,

This you can do in power Query merge opertion.

Take first and second values and merge as full outer. now take the third value and again merge with the previous output as full outer. Repeat same process for all the values. This will create all possible combinations and can be used in slicer for user selection. 

[iadbmd]

Will this allow me to analyze an existing table, which is imported using a query, and calculate variables while the users select desired values using the slicer? My current issue is that the calculations are not changing to the newly selected numbers from the dropdowns.

Example: Say the table is 1000x20. I need to evaluate: 

Result = 

If (Var1 == "Active" & Var3 == "Green"), G_Active

ElseIf(Var1==Ïnactive"& Var3=="Green"), G_Inactive
and so on...

The values G_Active and G_Inactive need to be dynamic values that the user can change, preferably using a dropdown. I have a slicer for those. I cannot add a new column to the table because that is static, so it will not react to the slicer. This is an example of the calculation I need to make:

1.1 Status =

VAR _phase = SELECTEDVALUE(Table1[Phase])

VAR _type  = SELECTEDVALUE(Table1[oper_typ_cd])

VAR _option1 = 'Parameter1'[Parameter1 Value]

VAR _option2 = 'Parameter2'[Parameter2 Value]

VAR _option3 = 'Parameter3'[Parameter3 Value]

VAR _option4 = 'Parameter4'[Parameter4 Value]

VAR result = SWITCH(

    TRUE(),

    _type = "Green" && _phase = "Active", _option1,

    _type = "Green" && _phase = "Inactive",   _option2,

    _type = "Blue" && _phase = "Active", _option3,

    _type = "Blue" && _phase = "Inactive",   _option4,

    0

)

RETURN result

The options were created using New Parameter.

[FBergamaschi]

One these variables are setup, what do you want to do with them?

[iadbmd]

The new variables (Value_1, Value_2 etc.) are used in other calculations downstream. My table is larger in reality. It contains identifiers such as Country and Year. I then aggregate the Values by Country-Year and take some averages to display.