Data Dictionary

Below is a list of calculations made in the Power BI report using DAX measures and calculated columns. The code for the measure or column can be used to translate to other tools (Tableau, etc.).

To access the downloadable excel file, access the gitHub link below:

Not found
Type
Calculation Name
Connected Visuals
Description
DAX Code

Measure

AssessmentParticipatingK12IdeaPercentREDACTED

1

Calculation for the percent of IDEA students who participated in the state assessment. Value is suppressed for count of participating IDEA students less than 10. 

AssessmentParticipatingK12IdeaPercentREDACTED = 
VAR AssessmentParticipatingK12IdeaCount =
    CALCULATE(
        COUNT('2) RDS vwStateAssessments'[K12StudentId]),
        'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated",
        'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes"
    )
VAR AssessmentParticipationK12IdeaCount = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] IN {"Participated", "DidNotParticipate"},
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes"
)
VAR AssessmentParticipatingK12IdeaPercent =
    DIVIDE(AssessmentParticipatingK12IdeaCount, AssessmentParticipationK12IdeaCount)

RETURN
    IF(AssessmentParticipatingK12IdeaCount < 10, BLANK(), AssessmentParticipatingK12IdeaPercent)

Measure

AlternateAssessmentK12IdeaPercentREDACTED

2

Percent of IDEA students who have participated in the alternate state assessment. Value is suppressed for count of IDEA students taking the alternate assessment under 10. 

AlternateAssessmentK12IdeaPercentREDACTED = 
VAR AlternateAssessmentK12IdeaCount = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated",
    'RDS DimAssessments'[Alternate Assessment Status] = "Alternate",
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes"
)
VAR AssessmentK12IdeaCount = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated",
    'RDS DimAssessments'[Alternate Assessment Status] IN {"Alternate", "Standard"},
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes"
)

VAR AlternateAssessmentK12IdeaPercent = 
DIVIDE(
    AlternateAssessmentK12IdeaCount,
    AssessmentK12IdeaCount
)

RETURN
IF(
    AlternateAssessmentK12IdeaCount < 10,
    BLANK(),
    AlternateAssessmentK12IdeaPercent
)

Measure

AlternateAssessmentK12StudentPercentREDACTED

3

Percent of all students who have opted to take the alternate state assessment. Value is suppressed for count of K12 students taking the alternate assessment under 10. 

AlternateAssessmentK12StudentPercentREDACTED = 
VAR AlternateAssessmentK12Count = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated",
    'RDS DimAssessments'[Alternate Assessment Status] = "Alternate"
)
VAR AssessmentK12Count = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated",
    'RDS DimAssessments'[Alternate Assessment Status] IN {"Alternate", "Standard"}
)
VAR AlternateAssessmentK12Percent = 
DIVIDE(
    AlternateAssessmentK12Count,
    AssessmentK12Count
)
RETURN
IF(
    AlternateAssessmentK12Count < 10,
    BLANK(),
    AlternateAssessmentK12Percent
)

Measure

AssessmentProficientK12IdeaProportion

4

Percent of IDEA students who scored proficient on the state assessment. 

AssessmentProficientK12IdeaProportion = 
DIVIDE(
    //Count of Proficient IDEA students
    CALCULATE(
    COUNTROWS('2) RDS vwStateAssessments'),
    '2) RDS vwStateAssessments'[ProficiencyStatus] = "PROFICIENT",
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes"),

    //Count of Proficent/NotProficient IDEA Students
    CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    '2) RDS vwStateAssessments'[ProficiencyStatus] IN {"PROFICIENT", "NOTPROFICIENT"},
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes"
)
)

Measure

AssessmentProficientProportion

4

Percent of all students who scored proficient on the state assessment. 

AssessmentProficientProportion = 
DIVIDE(
    //Count of Proficient IDEA students
    CALCULATE(
    COUNTROWS('2) RDS vwStateAssessments'),
    '2) RDS vwStateAssessments'[ProficiencyStatus] = "PROFICIENT"),

    //Count of Proficent/NotProficient IDEA Students
    CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    '2) RDS vwStateAssessments'[ProficiencyStatus] IN {"PROFICIENT", "NOTPROFICIENT"}
)
)

Measure

AssessmentProficientK12IdeaPercentREDACTED

5, 6, 7

The percent of IDEA students who have scored proficient on the state assessment. Value is suppressed for count of IDEA students who scored proficient on the state assessment under 10. 

AssessmentProficientK12IdeaPercent = 
VAR AssessmentProficientK12IdeaCount = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]), 
    '2) RDS vwStateAssessments'[ProficiencyStatus] = "PROFICIENT",
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes",
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated"
)
VAR AssessmentParticipatingK12IdeaCount = 
CALCULATE(
    COUNT('2) RDS vwStateAssessments'[K12StudentId]),
    'RDS DimAssessmentRegistrations'[AssessmentRegistrationParticipationIndicatorCode] = "Participated",
    'RDS DimIdeaStatuses'[IdeaIndicatorCode] = "Yes" 
)
VAR AssessmentProficientK12IdeaPercent =
DIVIDE(
    AssessmentProficientK12IdeaCount,
    AssessmentParticipatingK12IdeaCount
)
RETURN
IF(
    AssessmentProficientK12IdeaCount < 10,
    BLANK(),
    AssessmentProficientK12IdeaPercent
)

PreviousSetup GuideNextSupport

Last updated