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.

[Rupak_bi]

Hi @iadbmd ,

It will work. try and let me know. share pbix with sample data and desired output for better explainations.

[v-aatheeque]

Hi @iadbmd 

I wanted to follow up on your issue to see how things are progressing.
Were you able to implement the suggested approach successfully, or are you still encountering any challenges?
Please let me know the current status  we’re happy to assist further to get this fully resolved.

[iadbmd]

I am still struggling. Below are the definitions of what I have:

1. A measure

1.1 Status=
VAR _phase = SELECTEDVALUE(Database[Phase])
VAR _type  = SELECTEDVALUE(Database[Type])
VAR _option1 = 'LON Prep'[LON Prep Value]
VAR _option2 = 'LON EXE'[LON EXE Value]
VAR _option3 = 'TC PREP'[TC PREP Value]
VAR _option4 = 'TC EXE'[TC EXE Value]

VAR result = SWITCH(
    TRUE(),
    _type = "LON" && _phase = "Prep", _option1,
    _type = "LON" && _phase = "Exe",   _option2,
    _type = "TC" && _phase = "Prep", _option3,
    _type = "TC" && _phase = "Exe",   _option4,
    5
)
RETURN result

2. Each _option is defined as a disconnected table and has an associated dropdown slicer to allow the user to change the values for testing:

LON Prep = GENERATESERIES(0, 2.1, 0.1)

The issue is the measure is not reacting to changes in _option using the dropdown slicers. I need to create bar charts showing the average value of measure 1.1 Status by Location (another column in Database). Each Location can have multiple rows in Database, which can have any combination of values for Phase and Type.

[v-aatheeque · Accepted solution]

Hi @iadbmd 

To help me reproduce your scenario and better understand why the slicer selections aren’t affecting the measure, could you please share a small but representative sample dataset in a usable format.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[v-aatheeque]

Hi @iadbmd 

To help us reproduce and troubleshoot your scenario, we requested to share a small sample dataset/file. Could you please provide that when convenient?
It will allow us to give you a more accurate and tailored solution.

[v-aatheeque]

Hi @iadbmd 

Just checking in to see if your issue has been resolved. Were you able to implement the suggested approach and get the expected results?

If everything’s working now, that’s great!  If not, feel free to share any updates  I’m happy to help further.

[v-aatheeque]

Hi @iadbmd 

Just wanted to check on the current status of your issue.

Were you able to implement the suggested approach successfully, or are you still facing any issues.
Let us know where things stand  we’re happy to assist further if needed.

[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.