Forum Discussion

iadbmd's avatar
iadbmd
Frequent Visitor
1 year ago
Solved

How to create conditional variable with dynamic values?

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:

IDTypePhaseValue
1GreenActiveG_Active

2

BlueActiveB_Active

3

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

 

EDIT 1/20/25:

Below is a sample from my dataset. I need to evaluate the columns typ, envmntl, and phase and set them equal to dynamic values which the user can change using slicers, eg. _opt_LON_PREP_A. The example would be:

TEST_1 = 

IF typ = "LON" && phase = "PREPARATION" && envmntl = "A", _opt_LON_PREP_A,

IF typ = "LON" && phase = "PREPARATION" && envmntl = "B", _opt_LON_PREP_B,
IF typ = "LON" && phase = "EXECUTION"   && envmntl = "A", _opt_LON_EXEC_A.
Another example variable I need to create would be:

TEST_2 = 

IF yr= "2025" && cd= "SG", _opt_25_SG,

IF typ = "2023" && cd= "SG", _opt_23_SG
 
The final thing I need to do is summarize the results by cntry by taking the product of a bunch of these variables. 
result_final = average(test_1)*average(test_2)
idnumtypcdyrenvmntlcntryphase
43497SU-G1019LONSG2024ASUExecution
8453GU-L1164LONSG2025AGUPreparation
82457CO-T1841TCPSG2025 COExecution
88675EC-T1590TCPSG2025BSUPreparation
19378BO-T1391TCPSG2024ABOPreparation
38132PE-L1153LONSG2023AGUExecution
58549ME-T1431TCPSG2021BMEPreparation
60825RG-T4407TCPSG2024BCOExecution
37762VE0146LONSG2022AVEExecution

 

Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    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



15 Replies

    • iadbmd's avatar
      iadbmd
      Frequent Visitor
      1 year ago

      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.

    • iadbmd's avatar
      iadbmd
      Frequent Visitor
      7 months ago

      Hello,

      After a long time I have provided a sample table from my dataset in the posts' body. I included examples of what I want to calculate as well.

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    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's avatar
      iadbmd
      Frequent Visitor
      1 year ago

      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.
      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        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.