Forum Discussion
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 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.
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
7 Replies
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
- 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])
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)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