Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
)
What you describe should ideally all be handled by the data model directly. No extra switch statements required. You should use EVALUATEANDLOG and DAXDEBUGOUTPUT to see how impactful these multiple nested calculate statements are.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |