Intersect Function

[Anonymous]

I have a dataset that has categories to denote time periods. It looks like this:

Faculty	SubjectCode	Year	Term	Result	Flag < 70%
A	A001	2019	T1 2019	88.9
A	A002	2019	T1 2019	60	1
A	A003	2019	T2 2019	100
B	B001	2019	T3 2019	88.2
B	B002	2019	T1 2019	80
B	B003	2019	T2 2019	82.4
C	C001	2019	T1 2019	64	1
C	C002	2019	T1 2019	83.3
C	C003	2019	T1 2019	69	1
D	D001	2019	T1 2019	68	1
D	D001	2019	T2 2019	67	1
D	D001	2019	T3 2019	67	1
E	E001	2019	T2 2019	91.5
E	E002	2019	T3 2019	86.6
F	F001	2019	T1 2019	82.4
F	F002	2019	T2 2019	100
F	F003	2019	T2 2019	100
G	G001	2019	T3 2019	66	1
G	G002	2019	T1 2019	69	1
G	G002	2019	S2 2018	68	1
G	G004	2019	T1 2019	87.3
G	G005	2019	T2 2019	62	1
G	G006	2019	T1 2019	100
G	G007	2019	T2 2019	96.9

My time period table looks like this:

OrderSemester / Term MapTermPrevious Semester TermExclude SummerYearCalendarYearOrderYear_New

Order	Term	Previous Term	Year
1	S1 2017		2017
2	S2 2017	S1 2017	2017
3	S1 2018	S2 2017	2018
4	S2 2018	S1 2018	2018
5	T1 2019	S2 2018	2019
6	T2 2019	T1 2019	2019
7	T3 2019	T2 2019	2019
8	S1 2019	S2 2018	2019
9	S2 2019	S2 2019	2019

I am trying to answer the following questions:

1. What is the count of subjects that appear in more than one term for a specified time period - i.e. 2019 or both 2018 & 2019
2. For the subjects with less than 70% in a specific time period (e.g. the most recent term is T3 2019), has the same subject performed poorly in previous terms

The following table is the result for the time period 2018 - 2019 with the test table above.

Question 1 Answer:

Count of subjects with less than 70% in more than one term, and the number of terms the course performed poorly across both 2018 and 2019

	2018 - 2019	Count of terms
A
B
C
D	1	3
E
F
G	1	2

Question 2 issue:

I think I need to wrap another intersect function around this.

Need a bit of help with it though. 

[v-eachen-msft]

Hi @Anonymous ,

I don't quite understand your second question, do you want to return a TRUE/FALSE result? Or do you want to put into the result of question 1 as a condition?

[Anonymous]

Thanks, having re-read I most certainly could be clearer. 

My intention here is to be able to count the number of courses for a particular year or two, my calculation for this is uses a date function. However the term categorical data (term) I havent been able to figure it out. 

The question I need to answer is that if T3 2019 is selected, has a subject that has less than 70% for this term, also had less than 70% for two or three terms prior. I have been trying a version of the following unsuccessfully:

Test Intersect = 
VAR T1_2019 = CALCULATETABLE(VALUES('Summary'[Subject]),FILTER('Summary','Summary'[Semester / Term]="T1 2019"))
VAR T2_2019 = CALCULATETABLE(VALUES('Summary'[Subject]),FILTER('Summary','Summary'[Semester / Term]="T2 2019"))
VAR T3_2019 = VALUES('Summary'[Subject])

Return
COUNTROWS(
    INTERSECT(
        INTERSECT(T3_2019,T2_2019),T1_2019))

[PaulDBrown]

@Anonymous 

Do you need the period selection to be dynamic?

[Anonymous]

@PaulDBrown  yes if possible, thanks

[PaulDBrown · Accepted solution]

@Anonymous 

Well, that was harder than I envisaged! But I think I've cracked it.

Here are a few screenshots:

And when you slice by faculty and year:

and then add a slicer on Term:

Here is the PBIX File (please double check the results!)

Counting Subjects by Term 

[Anonymous]

@PaulDBrown  Thanks so very much for this, this is exactly what I need. Using the Period table Order is a great solution. This is greatly appreciated and soo quick.  

[PaulDBrown]

@Anonymous 

I actually realised a few minutes ago that I only included the measure which calculates the "Subjects in more than one Period (all Periods)" in the results table (in other words, it's oblivious to period selections). Apologies for that.

I have now updated the file and the results table includes the measure which reflects the "Subjects in more than one period (Selected Year)": in other words, it responds to the filter for Year (If a particular term is selected, the measure is redundant of course).

As an example:

Something worth mentioning is that the measures, placed in the same table, can lead to confusion if more than one value in the period slicers is selected (ie, more than one year, or more than one term. It works fine if you select one year and/or one term): it will be ok for the measure counting the number of subjects with more than one period, but the measures calculating the <70% are designed to reference a single period selected. It can be tweaked to cater for multiple selections, but I think that may lead to confusion in the user. FWIW.