Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditional Measure

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?

1 ACCEPTED SOLUTION

@Anonymous 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

12 REPLIES 12
ryan_mayu
Super User
Super User

@Anonymous 

could you pls provide the sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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 %PresentHT1 PtsHT2 %PresentHT2 PtsAverage%Total HT Pts
100.00%10095%097.5%100
98.47%0100%10050%100
95.56%0100%10050%100

@Anonymous 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

IDAttendance YearAttendance RegCurrent/Leaving YearCurrent/Leaving RegAM/PMMarkMark date     
1Year 1010S1010SAM#07-Sep-20 /Present Mark AM41 
1Year 1010S1010SPM#07-Sep-20 \Present Mark PM43 
1Year 1010S1010SAMX08-Sep-20 LLate (Counts as present)3 
1Year 1010S1010SPMX08-Sep-20  Present Marks87 
1Year 1010S1010SAM/09-Sep-20     
1Year 1010S1010SPM\09-Sep-20     
1Year 1010S1010SAM/10-Sep-20  07/09 - 11/09WK01HT1
1Year 1010S1010SPM\10-Sep-20     
1Year 1010S1010SAM/11-Sep-20     
1Year 1010S1010SPM\11-Sep-20     
1Year 1010S1010SAM#12-Sep-20     
1Year 1010S1010SPM#12-Sep-20     
1Year 1010S1010SAM#13-Sep-20     
1Year 1010S1010SPM#13-Sep-20     
1Year 1010S1010SAM/14-Sep-20     
1Year 1010S1010SPM\14-Sep-20     
1Year 1010S1010SAM/15-Sep-20     
1Year 1010S1010SPM\15-Sep-20     
1Year 1010S1010SAM/16-Sep-20     

@Anonymous 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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%. 

@Anonymous 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.