Tricky Switch Function using selection slicers

[Anonymous]

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 criteria selection from the YTD, QTD and MTD slicer and the Sceneria Slicer...

Option are:

AOP and MTD selected then AOP Total Management Measure

AOP and QTD Selected then AOP QTD Total Management Measure

AOP and MTD Selected then AOP MTD Total Management Measure

if PY Selected then the PY measures as defined in the PL Hyperion Summary Table

If nothing selected in AOP/PY Slicer then Use Total Managment Measure and QTD Total and YTD Total management measure , the ones less the AOP and PY pre-fix....

I am trying to cover all basis....here....stuck....

One Drive Link PBIX file.

https://1drv.ms/u/s!AjnJ6sK4WtA8a0L8RrfZZEp2gGE

[MFelix]

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]

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)

[Swamy3105]

@MFelix 

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

thanks,

Swamy

[MFelix · Accepted solution]

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]

Spot on.....God Bless

[Anonymous]

Just removed link 16...not getting the desired results...HELP..

[Anonymous]

Thanks m Felix excellent as always... Would I not need to also tooo the following with the AOP PY dimension see example from one of your posts. Table = SalesParameters ID    Parameter 1     Pieces 2     Value   Selected SalesParameter= MIN(SalesParameter[ID]) Selection = IF (HASONEVALUE ( SalesParameter[Parameter]), VALUES ( SalesParameter[Parameter]))     SALESVALUES = SUM(Sales[Sales_Value]) PIECESVALUES = SUM(Sales[Sales_Pieces]) SalesTotal = SWITCH([Selected SalesParameter],1,Sales[PIECESVALUES],2,Sales[SALESVALUES],BLANK(),[PIECESVALUES])