Am newish to Power Bi and Dax - all self taught at moment, although am working through videos and find these forums really helpful.
I have a problem which I just cannot get my head round how to solve.....
I am working with school term dates.
Have a "helper" table which has start and end dates for each term and also a term report date (which is at some point during a term). This also has a "Quarter" allocated to each term in appropriate order, ie Autumn, Spring, Summer.
Have set up a custom date table where I work out what week a date falls into within school year (term starts beginning September); and have defined a Term and Quarter calculated column based on lookup using helper table. So far so good..
I am working with Attendance data which has a column with a statistical meaning of present/absent for each day of each term. I have Many to One relationship between Attendance and Date Table.
I need to be able to have a table visual which shows a term and count of absent, count of present, % Attendance for term selected from a slicer but also needs to show previous term's data.
I hvae managed to create measures on Date table for:
All work perfectly, I am happy to say
I then use these measures in Attendance table to do a count of absent / present and calculate % Attendance
All work perfectly
Also done for the period up to selected term report date:
I think you should rewrite the measures that are in this form:
CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))
get rid of the calculate round the iterator, in fact get rid of the iterator and the assignment of 1 or 0, just FILTER the table using the clauses you have and COUNTROWS.
In the measure that doesn't work, you will have to debug it to see which clause or condition is causing no rows to be returned. You'll be in a better position if you rewrite according to the advice above.
Reduce the filter clauses until you start to get values returned, then add in another clause. Test to see if the measures e.g. [PreviousTermStartDate] are populated.
Obviously I don't have your data model and data but I'll try and help you debug it if you get stuck. Good luck
Rewrote measure as follows:
Here is data up to report date in each term
All terms and dates come from Date table and attendance data from Attendance table with many to one relationship based on date field.
I have a slicer based on Term.
If I do a table visual to pull out date information on currently selected term and previously selected term (measures in original post), it works correctly:
If I have a table visual to pull out attendance data using measures outlined above (with changes you suggested), I get this:
I want it to put the number of absent from the whole of Spring Term (in first table above) - so figure for CountAbsent-Previous should be 469.
Happy to share file but not sure of best way to do that...
Post your pbix on a 3rd party site that you trust e.g. box and i'll try and look at it this weekend.
On the re-written measures - they are still overly complex. You can combine the filter clauses rather than sending 3 versions of the filtered table as table filters.
I have literally just got to the solution with help from previous suggestion from @amitchandak that I have finally got my head around. Is still a bit of a fudge but I can live with it...
In Power Query have created conditional column to give each row that has Absent a value of 1 (will do likewise for present).
In my data table I have a quarter assigned to each term in appropriate order.
Managed to now get an answer with a measure using:
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.