topic Dates!! Not quite sure how to describe this in one line ....but need help in DAX Commands and Tips

Dates!! Not quite sure how to describe this in one line ....but need help

BLAWHEEM1 — Thu, 29 Oct 2020 17:21:22 GMT

Hi All

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:

selected term (from slicer)
selected quarter (used in order to allow me to identify previous quarter)
selected term start date / end date / report date
previous term
pervious quarter
previous term start date / end date / report date

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

CountAbsent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent",1,0)))

CountPresent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present",1,0)))

%PresentTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermEndDate]))

All work perfectly

Also done for the period up to selected term report date:

CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))

CountPresent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present" && Attendance[Mark date]<=[TermReportDate],1,0)))

%PresentTerm-Report = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermReportDate]))

Again these all work perfectly.

However I cannot get this to work for previous term (which would be whole of term), I know my measures are correct as I have tested by creating a table which shows me the selected term information (dates) as well as previous term information (dates)

I followed same principles for counting absent/previous as above, ie:

CountAbsent-Previous = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate],1,0)))

%PresentPreviousTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate]))

But the answer I get is zero for the counts and absolutely nothing for the %. Am baffled as to why this will not work when it works on the previous term information table. See attached screenshot.

Any suggestions (in idiot speak) would be gratefully received!!!

Re: Dates!! Not quite sure how to describe this in one line ....but need help

HotChilli — Thu, 29 Oct 2020 23:14:09 GMT

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

Re: Dates!! Not quite sure how to describe this in one line ....but need help

BLAWHEEM1 — Fri, 30 Oct 2020 07:46:03 GMT

Many thanks HotChilli will give it a go!

Re: Dates!! Not quite sure how to describe this in one line ....but need help

BLAWHEEM1 — Fri, 30 Oct 2020 08:26:35 GMT

Hi HotChilli

Rewrote measure as follows:

CountAbsent-ReportFilter =

COUNTROWS (

CALCULATETABLE (

Attendance,

FILTER (

Attendance,

Attendance[Statistical meaning] ="Absent"

FILTER (

Attendance,

Attendance[Mark date]>= [TermStartDate]

FILTER (

Attendance,

Attendance[Mark date]<= [TermReportDate]

)

This works correctly as is still looking at the term that is selected on the slicer

When I tried this one though

CountAbsent-Previous =

COUNTROWS (

CALCULATETABLE (

Attendance,

FILTER (

Attendance,

Attendance[Statistical meaning] ="Absent"

FILTER (

Attendance,

Attendance[Mark date]>= [PreviousTermStartDate]

FILTER (

Attendance,

Attendance[Mark date]<= [PreviousTermEndDate]

)

I get nothing at all (previously I did get zero with the measure I wrote (see original post)).

I am thinking that the slicer on Term (from date table) is filtering the attendance table so that I actually don't have any rows with the dates I want in it. I am not sure how to get around this as I need the slicer to work on the table visual as I want to show data for the selected term but need to also show previous term data on same row of table visual.

Here is the data for whole terms:

Term	CountAbsent	CountPresent	%PresentYTD	TermStartDate	TermEndDate
Autumn	443	7853	94.66%	02/09/2019	07/01/2020
Spring	469	5777	92.49%	08/01/2020	19/04/2020
Summer	43	6301	99.32%	20/04/2020	31/08/2020

Here is data up to report date in each term

Term	CountAbsent-Report	CountPresent-Report	%PresentTerm-Report	TermStartDate	TermReportDate
Autumn	321	6389	95.22%	02/09/2019	29/11/2019
Spring	292	4100	93.35%	08/01/2020	04/03/2020
Summer	32	4726	99.33%	20/04/2020	19/06/2020

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:

Quarter	Term	TermStartDate	TermEndDate	TermReportDate	PreviousQuarter	PreviousTerm	PreviousTermStartDate	PreviousTermEndDate
4	Summer	20/04/2020	31/08/2020	19/06/2020	3	Spring	08/01/2020	19/04/2020

If I have a table visual to pull out attendance data using measures outlined above (with changes you suggested), I get this:

Term	CountAbsent	CountPresent	%PresentTerm	CountAbsent-Report	CountAbsent-ReportFilter	CountPresent-Report	%PresentTerm-Report	CountAbsent-Previous
Summer	43	6301	99.32%	32	32	4726	99.33%

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

Re: Dates!! Not quite sure how to describe this in one line ....but need help

HotChilli — Fri, 30 Oct 2020 15:06:27 GMT

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.

Re: Dates!! Not quite sure how to describe this in one line ....but need help

BLAWHEEM1 — Fri, 30 Oct 2020 15:12:09 GMT

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:

CountAbsent-Previous = CALCULATE(sum(Attendance[AbsentValue]), FILTER(ALL('Academic Date Table'), 'Academic Date Table'[Quarter]=max('Academic Date Table'[Quarter])-1))

Thanks so much for your offer of help HotChilli

Am gradually getting my head around things (sure I will be back though!!).

This has significantly reduced the work in my previous incarnation which had a lot of "bodges" and extra queries as well as wide tables to get to the same answers!!!!