Changing measure calculation based on Column values.

[sunilbeckham]

Hello everyone

I have a student table similar to the below dummy columns that I'm trying to create a measure for. I'm trying to create a measure that will change it's calculation depending on the values in the column "Completion Years". The intent is to create a measure which will show the percentage of students, but the denominator will change depending on the column values of "File Source". Here's the measure that I have created so far:

Graduation Rate =

SWITCH(TRUE(), student[Completion Years] = "3 Years", DIVIDE(CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] = "OT",student[Source] = "On Time"),CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] = "OT",student[Source] = "On Time")),student[Completion Years] IN{ "4 Years","5 Years"},DIVIDE(CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] IN {"OT", "ET"},student[Source] = "Extended Time"),CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] IN {"OT","ET"} ,student[Source] = "Extended Time")),student[Completion Years])

However, the intellisence is not picking up the column name "Completion Years". Is there something that I'm missing out on?

Any help would be much appreciated.

Thanks.

Student Number	Graduation Status	Source	Completion Years
101	OT	On Time	3 Years
102	OT	On Time	3 Years
103	ET	Extended Time	4 Years
104	ET	Extended Time	5 Years
105	ET	Extended Time	5 Years

[onurbmiguel_ · Accepted solution]

hi @sunilbeckham 

Try this measure: 

VAR _compl_year = SELECTEDVALUE( Student[Completion Years] )
VAR _value =
    SWITCH(
        TRUE( ),
        _compl_year = "3 Years",
            DIVIDE(
                CALCULATE( COUNTA( Student[Student Number] ), Student[Graduation Status] = "OT", Student[Source] = "On Time" ),
                CALCULATE( COUNTA( Student[Student Number] ), Student[Graduation Status] = "OT", Student[Source] = "On Time" )
            ),
        _compl_year IN { "4 Years", "5 Years" },
            DIVIDE(
                CALCULATE(
                    COUNTA( Student[Student Number] ),
                    Student[Graduation Status] IN { "OT", "ET" },
                    Student[Source] = "Extended Time"
                ),
                CALCULATE(
                    COUNTA( Student[Student Number] ),
                    Student[Graduation Status] IN { "OT", "ET" },
                    Student[Source] = "Extended Time"
                )
            ),
        BLANK( )
    )
RETURN
    _value

Best regards

Bruno Costa | Power Participant

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍


You can also check out BI4ALL's website and our data solutions!

[sunilbeckham]

This was really helpful.

Thank you so much for your time and input.