Conditional Measure

[Anonymous]

Good Afternoon 

I am having an issue with finding a DAX and I was hoping someone on here may be able to help me. I have a measure that is working out the percentage attendance of students in a school. I want to then award points to the students who have achieved 100% in the term. The calendar for the terms is in a seperate table which I created in power bi. this table is linked to the report for attendance byt the date field in both tables. 

everything ive looked at seems to be to return a text field. but I need a numerical return so that I can do a calculation of points achieved overall etc. Can anyone help?

[ryan_mayu]

@Anonymous 

could you pls provide the sample data and expected output?

[Anonymous]

Yes no problem 

So I have a CSV with various marks entered in a register for students. I have then in a measure calculated the rows that have the present attendance marks against the possible to give me the percentage attendance. Based on the date range of the marks against the academic calendar would determine the column marks HT1, HT2, HT3 etc (these are the term names). There is a relationship between the two tables.

The students that have recieved 100% for the term should recieve 100pts. I then needs to do this by week so if there is a DAX i could just amend this to reflect the week rather than term. 

HT1 %Present	HT1 Pts	HT2 %Present	HT2 Pts	Average%	Total HT Pts
100.00%	100	95%	0	97.5%	100
98.47%	0	100%	100	50%	100
95.56%	0	100%	100	50%	100

[ryan_mayu]

@Anonymous 

this is the expected output. Could you pls provide some sample data? maybe that CSV file?

[Anonymous]

So the outcome here is I need to a DAX to calculate if the Attendance % for HT1 = 100 then return the Value 100 is else then 0. What I need though is for the Term total and week total to be be returned in seperate columns/Measures that i could then add up at the end so kind of like a running total of points 

[Anonymous]

yes just give me a few minutes its an anormous file so im just going to snap some of the data out thats anonomised

[Anonymous]

ID Attendance Year Attendance Reg Current/Leaving Year Current/Leaving Reg AM/PM Mark Mark date 1 Year 10 10S 10 10S AM # 07-Sep-20 / Present Mark AM 41 1 Year 10 10S 10 10S PM # 07-Sep-20 \ Present Mark PM 43 1 Year 10 10S 10 10S AM X 08-Sep-20 L Late (Counts as present) 3 1 Year 10 10S 10 10S PM X 08-Sep-20 Present Marks 87 1 Year 10 10S 10 10S AM / 09-Sep-20 1 Year 10 10S 10 10S PM \ 09-Sep-20 1 Year 10 10S 10 10S AM / 10-Sep-20 07/09 - 11/09 WK01 HT1 1 Year 10 10S 10 10S PM \ 10-Sep-20 1 Year 10 10S 10 10S AM / 11-Sep-20 1 Year 10 10S 10 10S PM \ 11-Sep-20 1 Year 10 10S 10 10S AM # 12-Sep-20 1 Year 10 10S 10 10S PM # 12-Sep-20 1 Year 10 10S 10 10S AM # 13-Sep-20 1 Year 10 10S 10 10S PM # 13-Sep-20 1 Year 10 10S 10 10S AM / 14-Sep-20 1 Year 10 10S 10 10S PM \ 14-Sep-20 1 Year 10 10S 10 10S AM / 15-Sep-20 1 Year 10 10S 10 10S PM \ 15-Sep-20 1 Year 10 10S 10 10S AM / 16-Sep-20

[Anonymous]

ID

Attendance Year

Attendance Reg

Current/Leaving Year

Current/Leaving Reg

AM/PM

Mark

Mark date

1

Year 10

10S

10

10S

AM

#

07-Sep-20

/

Present Mark AM

41

1

Year 10

10S

10

10S

PM

#

07-Sep-20

\

Present Mark PM

43

1

Year 10

10S

10

10S

AM

X

08-Sep-20

L

Late (Counts as present)

3

1

Year 10

10S

10

10S

PM

X

08-Sep-20

Present Marks

87

1

Year 10

10S

10

10S

AM

/

09-Sep-20

1

Year 10

10S

10

10S

PM

\

09-Sep-20

1

Year 10

10S

10

10S

AM

/

10-Sep-20

07/09 - 11/09

WK01

HT1

1

Year 10

10S

10

10S

PM

\

10-Sep-20

1

Year 10

10S

10

10S

AM

/

11-Sep-20

1

Year 10

10S

10

10S

PM

\

11-Sep-20

1

Year 10

10S

10

10S

AM

#

12-Sep-20

1

Year 10

10S

10

10S

PM

#

12-Sep-20

1

Year 10

10S

10

10S

AM

#

13-Sep-20

1

Year 10

10S

10

10S

PM

#

13-Sep-20

1

Year 10

10S

10

10S

AM

/

14-Sep-20

1

Year 10

10S

10

10S

PM

\

14-Sep-20

1

Year 10

10S

10

10S

AM

/

15-Sep-20

1

Year 10

10S

10

10S

PM

\

15-Sep-20

1

Year 10

10S

10

10S

AM

/

16-Sep-20

[ryan_mayu]

@Anonymous 

now i see H1 is from 7 sep to 11 sep. then what's the calculation logic of present?

[Anonymous]

Present is any Mark on a register for AM it is / and for PM register is \. The L code is Late before Registration which is a present mark but gives us the ability to track the number of lates. The total number of marks is the count of all marks in the column and then the total present including L / by the total to give the % attendance. so if the Period is HT1 then the pts achieved is 100 if the attendance for the period is 100%. 

[ryan_mayu · Accepted solution]

@Anonymous 

pls see the attachment below

[Anonymous]

Thank you for this I cant believe you made it look so easy ive been pulling my hair out.... REALLY APPRECIATED

[ryan_mayu]

you are welcome