topic Calculating the number of occasions in DAX Commands and Tips

Calculating the number of occasions

Anonymous — Fri, 20 Aug 2021 13:03:23 GMT

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

Re: Calculating the number of occasions

Greg_Deckler — Fri, 20 Aug 2021 13:24:19 GMT

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.

Re: Calculating the number of occasions

Anonymous — Fri, 20 Aug 2021 13:55:10 GMT

Greg_Deckler

Hi Greg,

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

Re: Calculating the number of occasions

Anonymous — Fri, 20 Aug 2021 14:03:03 GMT

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

Re: Calculating the number of occasions

Greg_Deckler — Fri, 20 Aug 2021 14:05:59 GMT

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.

Re: Calculating the number of occasions

Anonymous — Fri, 20 Aug 2021 14:19:52 GMT

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