I have a measure where the desired output is dependent on user selection of about 4 slicers. I've tried doing this all as variables and all as separate measures but there's a breakdown somewhere. I don't know if I should be using something like KEEPFILTERS.
Here's a rough explanation of what I'm trying to achieve.
step1 = SWITCH( SELECTEDVALUE(C_DateView[View]),
"YTD",TOTALYTD(SUM(FACT_PL[USDAMOUNT]),DIM_DATE[FULLDATE],DIM_DATE[FULLDATE] >= startY && DIM_DATE[FULLDATE] <= endDate),
"QTD", TOTALQTD(SUM(FACT_PL[USDAMOUNT]),DIM_DATE[FULLDATE],DIM_DATE[FULLDATE] >= startQ && DIM_DATE[FULLDATE] <= endDate),
"MTD", TOTALMTD(SUM(FACT_PL[USDAMOUNT]),DIM_DATE[FULLDATE],DIM_DATE[FULLDATE] >= startM && DIM_DATE[FULLDATE] <= endDate),
SUM(FACT_PL[USDAMOUNT])
)
step2 = SWITCH( SELECTEDVALUE(C_Allocation[AllocationBool]),
"Yes",CALCULATE(step1,FACT_PL[ALLOCATEDFLAG],FACT_PL[ALLOCATEDFLAG] = 1 || FACT_PL[ALLOCATEDFLAG] = -1),
"No",CALCULATE(step1,FACT_PL[ALLOCATEDFLAG],FACT_PL[ALLOCATEDFLAG] = 1 || FACT_PL[ALLOCATEDFLAG] = -0),
CALCULATE(step1,FACT_PL[ALLOCATEDFLAG],FACT_PL[ALLOCATEDFLAG] = 1 || FACT_PL[ALLOCATEDFLAG] = -1)
)
step3 = SWITCH( SELECTEDVALUE(C_RptFlag[Reporting Flag]),
"One-Time",CALCULATE(step2 ,DIM_REPORTINGFLAG[REPORTINGFLAG] = "One time"),
"Exclude One-Time",CALCULATE(step2 ,DIM_REPORTINGFLAG[REPORTINGFLAG] <> "One time"), step2 )
step4 = SWITCH( SELECTEDVALUE(C_DisplayUnits[Type]),
"Thousands", step3/1000 ,
"Millions", step3/1000000 ,
step3
)
I can ditch the TOTAL?TD formulas and just do old style if necessary. Step 1 & 3 use DIM tables as filters, the rest are contained within the fact table.
Something hinky is going on between Step 2 and 3 I think where the values zero out completely as if the entire table is filtered to nothing which shouldn't be true.
I thought about making an IF statement with roughly 54 conditions but I'm guessing there's gotta be a better way.
