Forum Discussion
Intersect Function
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:
- 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
- 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.
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!)
7 Replies
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?
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))Anonymous
Do you need the period selection to be dynamic?
