Calculating the number of occasions

[Anonymous]

Good Afternoon 

i have a bit of an issue with ca calculation that I need 

i have a table created in Power BI which is a date table with the usual columns i.e. week, week day etc. I then have a table of marks from registers for students, ive then worked out the percentage for the weeks and over the course of the year. however I need to count the number of times a student has been 100% in weeks. for for example i need to value returned to be something like the below 

Week 1 100%, Week 2 100%, Week 3 50% = 2. 

I have been looking at this now for just over a week and I may well have over worked it but im stuck. 

Any help would be appreciated 

Kind Regards

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler 

Hi Greg,

thank you for coming back to me i will send you what I can Date TableAttendance Table

[Anonymous]

@Greg_Deckler 

So what ive had to do is work out from the marks which are present and which are not. I have then worked out the % attendance based on the marks used, the follow dax is what ive used 

%Present% = CALCULATE(COUNTROWS('BSCA Attendance'),FILTER('BSCA Attendance','BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="/" ||'BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="L"||'BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="B"||'BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="J"||'BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="P"||'BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="V"||'BSCA Attendance'[AM/PM]="AM"&&'BSCA Attendance'[Mark]="W"||'BSCA Attendance'[AM/PM]="PM" && 'BSCA Attendance'[Mark]="\"||'BSCA Attendance'[AM/PM]="PM" && 'BSCA Attendance'[Mark]="L"||'BSCA Attendance'[AM/PM]="PM" && 'BSCA Attendance'[Mark]="B"||'BSCA Attendance'[AM/PM]="PM" &&'BSCA Attendance'[Mark]="J"||'BSCA Attendance'[AM/PM]="PM" && 'BSCA Attendance'[Mark]="P"||'BSCA Attendance'[AM/PM]="PM"&& 'BSCA Attendance'[Mark]="V"||'BSCA Attendance'[AM/PM]="PM" &&'BSCA Attendance'[Mark]="W"))/CALCULATE(COUNTROWS('BSCA Attendance'),FILTER('BSCA Attendance', 'BSCA Attendance'[Mark]<>"Y" && 'BSCA Attendance'[Mark]<>"X" && 'BSCA Attendance'[Mark]<>"-" && 'BSCA Attendance'[Mark]<>"Z" && 'BSCA Attendance'[Mark]<>"D" && 'BSCA Attendance'[Mark]<>"7" && 'BSCA Attendance'[Mark]<>"8" && 'BSCA Attendance'[Mark]<>"#" && 'BSCA Attendance'[Mark]<>BLANK()))

From this I then need to know how many weeks 100% attendance

[Greg_Deckler]

@Anonymous Thanks but can you please paste the data as text? Think about it, to recreate and test this I would have to manually type in all of that data. That is not going to happen.

[Anonymous]

@Greg_Deckler 

Date	Academic Year	Year	Day	Month	Term	Calendar Week	Week
05/09/2016 00:00:00	2016/17	2016	Mon	September	HT1	37	WK01
06/09/2016 00:00:00	2016/17	2016	Tue	September	HT1	37	WK01
07/09/2016 00:00:00	2016/17	2016	Wed	September	HT1	37	WK01
08/09/2016 00:00:00	2016/17	2016	Thu	September	HT1	37	WK01
09/09/2016 00:00:00	2016/17	2016	Fri	September	HT1	37	WK01
10/09/2016 00:00:00	2016/17	2016	Sat	September	HT1	37	WK01
11/09/2016 00:00:00	2016/17	2016	Sun	September	HT1	38	WK02
12/09/2016 00:00:00	2016/17	2016	Mon	September	HT1	38	WK02
13/09/2016 00:00:00	2016/17	2016	Tue	September	HT1	38	WK02
14/09/2016 00:00:00	2016/17	2016	Wed	September	HT1	38	WK02
15/09/2016 00:00:00	2016/17	2016	Thu	September	HT1	38	WK02
16/09/2016 00:00:00	2016/17	2016	Fri	September	HT1	38	WK02