Forum Discussion

Marcus352's avatar
Marcus352
Regular Visitor
3 years ago
Solved

Switch DAX

Hi all,

 

I've been attempting to address my problem for more than a week.
The following is a dax formula that I have.

Basically, when Course Level Name is selected, the "Var Overall RR" should provide me the data, whereas Overall RR CT should give me the data when Course Level Name is not selected. Unfortunately, the table disappears when I play with slicers.

Below is my dax. I can't seem to get it to operate. Could someone please assist.

 

Base Count =
Var SlicerRegional = SELECTEDVALUE(DIM_STUDENT_COHORT[REGIONAL_REMOTE_STATUS])
Var SlicerCommencing = SELECTEDVALUE(DIM_STUDENT_COHORT[STAGE_OF_STUDIES])
Var SlicerCitizenship = SELECTEDVALUE(DIM_STUDENT_COHORT[CITIZENSHIP_STATUS])
Var SlicerModeOfAttandance = SELECTEDVALUE(DIM_MODE_AND_TYPE_OF_ATTENDANCE[MODE_OF_ATTENDANCE])
Var SlicerTypeOfAttendance = SELECTEDVALUE(DIM_MODE_AND_TYPE_OF_ATTENDANCE[TYPE_OF_ATTENDANCE])
Var SlicerDisabilityStatus = SELECTEDVALUE(DIM_STUDENT_COHORT[DISABILITY_STATUS])
Var SlicerESB = SELECTEDVALUE(DIM_STUDENT_COHORT[ENGLISH_SPEAKING_BACKGROUND (groups)])
Var SlicerGender = SELECTEDVALUE(DIM_STUDENT_COHORT[GENDER])
Var SlicerIndigenousStatus = SELECTEDVALUE(DIM_STUDENT_COHORT[INDIGENOUS_STATUS])
Var SlicerMAC = SELECTEDVALUE(DIM_STUDENT_COHORT[MATURE_AGE_CATEGORY])
Var SlicerSES = SELECTEDVALUE(DIM_STUDENT_COHORT[SOCIO_ECONOMIC_STATUS])
Var SlicerCourseLevel = SELECTEDVALUE(DIM_COURSE_LEVEL[COURSE_LEVEL_NAME])
Var SlicerBFOE = SELECTEDVALUE(DIM_BROAD_FIELD_OF_EDUCATION[BROAD_FIELD_OF_EDUCATION_DESCRIPTION])

Var Overall_RR = CALCULATE(SUM(TXN_FACT_GOVERNMENT_BENCHMARK[Completion_COUNT]),DIM_STUDENT_COHORT[REGIONAL_REMOTE_STATUS] = "Total", DIM_STUDENT_COHORT[STAGE_OF_STUDIES] = "Total")

Var Overall_RR_CT = CALCULATE(SUM(TXN_FACT_GOVERNMENT_BENCHMARK[COMPLETION_COUNT]),DIM_STUDENT_COHORT[REGIONAL_REMOTE_STATUS] = "Total", DIM_STUDENT_COHORT[STAGE_OF_STUDIES] = "Total", DIM_JUNK_BENCHMARK_ANALYSIS[COLUMN_LEVEL_SUB_TOTAL] = "Sub-Total")

Return
SWITCH(TRUE(),
SlicerRegional = BLANK()
&& SlicerCommencing = BLANK()
&& SlicerCitizenship <> BLANK()
|| SlicerModeOfAttandance <> BLANK()
|| SlicerTypeOfAttendance <> BLANK()
|| SlicerDisabilityStatus <> BLANK()
|| SlicerESB <> BLANK()
|| SlicerGender <> BLANK()
|| SlicerIndigenousStatus <> BLANK()
|| SlicerMAC <> BLANK()
|| SlicerSES <> BLANK(),
Overall_RR,

SlicerCitizenship <> BLANK()
|| SlicerModeOfAttandance <> BLANK()
|| SlicerTypeOfAttendance <> BLANK()
|| SlicerDisabilityStatus <> BLANK()
|| SlicerESB <> BLANK()
|| SlicerGender <> BLANK()
|| SlicerIndigenousStatus <> BLANK()
|| SlicerMAC <> BLANK()
|| SlicerSES <> BLANK()
|| SlicerBFOE <> BLANK(),
Overall_RR_CT)

Thanks in advance

2 Replies