Using Multiple If or switch in Measures

[Anonymous]

Hi ,

I have a list of record in one table which is associated with different school for example like this:

School            TeacherperStudent    Date 

RV                        100                         1 Jan
Loyola                  150                         1 Jan
Convent               270                         1 Jan

RV                        120                         2 Jan

Loyola                  140                         2 Jan

DBMS                   700                         1 Jan

and another table where I have Target for each school like:

School        Target
RV                  110

Loyola            120

Convent         200

DBMS             70

I need to check if on dats if the Teacherperstudent has crossed the target or not and accordingly i want to highlight it in color.

The best approach will be to create a measure which will record 1 or 0 whenever school is within target or outside the target and based on the target I can apply conditional formatting.

I would like to take some help on the measure. I created calculated coloumn but not able to create measure. Measure is required because conditional formatting wont work without Measure.

Please let me know what will be formulae for this. how can i use switch in Measure.

Thanks.

Ankku

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

You can create a measure below:

WithinTarget = IF(SUM(Table1[TecherperStudent])-SUM(Table2[Target])<=0,1,0)

Then you can set the Conditional Formatting for the column TeacherperStudent. For more information, see attached report. 

Best Regards,
Qiuyun Yu