cancel
Showing results for
Did you mean:
Highlighted
Member

## 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:

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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

## Re: Intersect Function

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!)

If this post has helped you, please give it a thumbs up!
Thank you!

Proud to be a Super User!

7 REPLIES 7
Community Support Team

## Re: Intersect Function

Hi @Keelin ,

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?

If this post helps, then please consider Accept it as the solution to help the other members find it.
Member

## Re: Intersect Function

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))``````

Super User I

## Re: Intersect Function

Do you need the period selection to be dynamic?

If this post has helped you, please give it a thumbs up!
Thank you!

Proud to be a Super User!

Member

## Re: Intersect Function

@PaulDBrown  yes if possible, thanks

Super User I

## Re: Intersect Function

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!)

If this post has helped you, please give it a thumbs up!
Thank you!

Proud to be a Super User!

Member

## Re: Intersect Function

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

Super User I

## Re: Intersect Function

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.

If this post has helped you, please give it a thumbs up!
Thank you!

Proud to be a Super User!

Announcements