Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Tricky Switch Function using selection slicers

Hi Experts   See Attached file link on one drive i am trying to write a swicth function - see folder PL Hyperion Summary  - Measure "Test". Which give the net sale value based on the selection cri...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous,

     

    Try to simplify and make the measure below.

     

    Test =
VAR Timeframe =
    SELECTEDVALUE ( Periods[Period] )
VAR Selction =
    SELECTEDVALUE ( Activity[Scenario] )
VAR AOP =
    SWITCH (
        Timeframe;
        "MTD"; [AOP Total Management];
        "QTD"; [AOP QTD Total Management];
        "YTD"; [AOP YTD Total Management];
        [AOP Total Management]
    )
VAR PY =
    SWITCH (
        Timeframe;
        "MTD"; [PY Total Management];
        "QTD"; [PY QTD Total Management];
        "YTD"; [PY YTD Total Management];
        [PY QTD Total Management]
    )
RETURN
    SWITCH ( Selction; "AOP"; AOP; "PY"; PY; BLANK () )

    I'm making one variable for AOP another for PY this will had the possbility to have line 16 in the formula, on the last line I'm returning to the variables to get the result.

     

    Please check if you have the expected result for all alternatives.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
7 years ago

Hi Anonymous,

 

You have added a parameter in the switch function that is not making the comparision then it breaks out the rest of the function and you are make the Measure as the Values you want to compare and the comparision as the result.

 

Check the line 16 on your measure that should not be there.

 

Regards,

 

MFelix

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Test = 
VAR Timeframe = SELECTEDVALUE(Periods[Period])
VAR Selction = SELECTEDVALUE(Activity[Scenario])

RETURN 

SWITCH(TRUE(),AND(
    Selction = "AOP",
    Timeframe = "MTD"), [AOP Total Management],
            AND(
    Selction  = "AOP",
    Timeframe = "QTD"), [AOP QTD Total Management],
            AND(
    Selction  = "AOP",
    Timeframe = "YTD"), [AOP YTD Total Management],
            
            AND(
    Selction = "PY",
    Timeframe = "MTD"), [PY Total Management],
            AND(
    Selction  = "PY",
    Timeframe = "QTD"), [PY QTD Total Management],
            AND(
    Selction  = "PY",
    Timeframe = "YTD"), [PY YTD Total Management],
            [Net Sales],
        BLANK(),0)
  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous,

     

    Try to simplify and make the measure below.

     

    Test =
VAR Timeframe =
    SELECTEDVALUE ( Periods[Period] )
VAR Selction =
    SELECTEDVALUE ( Activity[Scenario] )
VAR AOP =
    SWITCH (
        Timeframe;
        "MTD"; [AOP Total Management];
        "QTD"; [AOP QTD Total Management];
        "YTD"; [AOP YTD Total Management];
        [AOP Total Management]
    )
VAR PY =
    SWITCH (
        Timeframe;
        "MTD"; [PY Total Management];
        "QTD"; [PY QTD Total Management];
        "YTD"; [PY YTD Total Management];
        [PY QTD Total Management]
    )
RETURN
    SWITCH ( Selction; "AOP"; AOP; "PY"; PY; BLANK () )

    I'm making one variable for AOP another for PY this will had the possbility to have line 16 in the formula, on the last line I'm returning to the variables to get the result.

     

    Please check if you have the expected result for all alternatives.

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Spot on.....God Bless

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    MFelix 

    Very fantastic measure....... it really worked for my scenario.

     

    thanks,

    Swamy