PowerBIforSchools

Define Academic Year of Date - Calculated Column It's handy to be able to classify dates by academic year. On a small table, like Exclusions, I'll do this as a calculated column. Let's say I have a field called ExclusionDate with the data type date (duh!) and I want to produce an academic year in the format "2018/19". We'll make a calculated column using the following formulas: IF - (<logical test> , <value if true>, <value if false) MONTH  (<date>) - returns numerical value 1 to 12 for Jan to December of the month of the selected date YEAR (<date>) returns the numerical value of the year of the selected date Basically, our formula is going to test if the date is in Sept - Dec or Jan - Aug , then perform two calculations to get the numbers either side of the "/" =IF(MONTH(Exclusions[ExclusionDate])>8,YEAR(Exclusions[ExclusionDate])&"/"&YEAR(Exclusions[ExclusionDate])-1999,YEAR(Exclusions[Exc...

One of the really powerful features of BI is being able to drill very quickly from the macro picture to the micro. To help schools do this with assessment results I've recently been working on this table showing subject, class and pupil level results (p2 of the report below). You can download the PBI file here , the class information looks like this  In a previous post I described concatenating studentid and subject to link classes, assessments and students, but that's not actually best practice - you can do it more simply with this setup I created the Subjects table using Dax (create table on the modelling tab). The formula is  Subjects = DISTINCT(AssessmentResults[Subject]). It gives a one column table with each distinct value of subject.  Note that here it is assumed that StudentClasses[Subject] isn't always the same AssessmentResults[Subject], as for example English Classes will often have English Literature Results. To handle...

Hi BI Team, Sorry I haven't posted in a while - I'm back working full time on BI so hopefully I'll fix that over the next few weeks. One thing that has been bugging me - literally - is the fact that applying a Bookmark to 'Selected Visuals' doesn't work as expected (the bookmarks just do nothing). This is super annoying as if you're trying to create a multipurpose dashboard it's useful to have bookmarks affect only part of the page. There is a workaround from user Denpries which you can find here https://community.powerbi.com/t5/Desktop/Bookmark-selected-visuals-bug-gt-almost-impossible-to-work-with/td-p/348111 Hopefully it'll get fixed soon.

Buttons and Bookmarks One of the really cool thing you can do in Power BI is make your dashboards interactive, allowing the user to control the slicer selections and the pages they see using buttons. You do this using 'Bookmarks' which are a saved state of all or part of your report. Consider this very simple example where we have two tables, one with FSM data and one with EAL and we want button controls to determine which is visible. The first step is to go the View ribbon and show the Selection Pane (which lists all the elements on the current page) and the Bookmarks pane. As you can see our two tables are both called 'Table' so the first thing we need to do is sort that out. In the format menu of the visuals click to show their title and give them a name. You can then hide the title again if you don't want it visible on the page, in the selection pane they are now listed under their title. Next to each item in the selection pane is an eye ...

I haven't really talked about visuals much as they're mostly pretty intuitive in BI but there is an issue if you want to produce a graph like this as the obvious steps won't get you there. So that's a bar chart showing the percentage of kids of each gender at low, middle high prior attainment from this Students table. But if you create the bar chart with Gender as the Axis, Prior Attainment as the Legend and Count of student ID as the value you get this: If you click the down arrow next to count of studentid you can use 'show value as....percent of grand total'  but that gives you: (note figures were added by going to the format (roller paint icon of the visual) and turning data labels on) The problem here is that the totals for each segment are being divided by all pupils not by Male/Female totals respectively. Row and Filter Context We can sort this out, but to understand how we need to understand two very important concepts in power...

In this post I'm going to show how look back in time at your school population on or between two dates. At Bromcom we've just added information on class, year memberships etc which we are calling 'collections' of pupils to distinguish them from the Year and Tutor  Group  fields on the Students table. Our database structure for this is pretty logical: Collections : A master list of groups with IDs and a group type (today I'm using the example of Year Groups which have the CollectionTypeName "OnRollYGrp") Collection Associates : A group memberships table that lists Collection IDs, StudentIDs and the Start and End dates of the membership of the group. CollectionAssociates should relate to Students on SchoolStudentId and to Collections on SchoolCollectionId If you want to play along you can download an excel file with these tables  here Count Members of a year group The first step is to make a measure on CollectionAssociates counting ...

It's often useful to have static data tables in your BI model (one recent example for me is converting ethnicity codes to census categories) and I've been through a process of trial and error to find the best way of doing this. Import from Excel/CSV This is the most obvious: Get Data, Excel, Bosh! But unless you're only going to update your data from PBI Desktop it's not a good idea. If you have Excel/CSV data sources in the file then you can't schedule refresh from  app.powerbi.com .  (you can if you set up a "gateway" and turn your machine into a server but, trust me, this isn't worth it) Google Sheet Next option is a Google Sheet and note that it must be a Google sheet, not simply an xlsx uploaded to Drive.  (Wondering why not an Excel 365 file as it's MICROSOFT Power BI? Hahahaha - you work it out then tell me how please ;)  To connect that to BI: Hit the share button top right hand side of the screen and edit the li...