cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Keelin Member
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.

 

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Intersect Function

@Keelin 

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

 

Here are a few screenshots:

All result.JPG

 

And when you slice by faculty and year:Year Slicer.JPG

 

and then add a slicer on Term:

Term Slicer.JPG

 

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

Counting Subjects by Term 




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Community Support Team
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?

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Keelin Member
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
Super User I

Re: Intersect Function

@Keelin 

 

Do you need the period selection to be dynamic?




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!




Keelin Member
Member

Re: Intersect Function

@PaulDBrown  yes if possible, thanks

Super User I
Super User I

Re: Intersect Function

@Keelin 

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

 

Here are a few screenshots:

All result.JPG

 

And when you slice by faculty and year:Year Slicer.JPG

 

and then add a slicer on Term:

Term Slicer.JPG

 

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

Counting Subjects by Term 




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!




View solution in original post

Keelin Member
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
Super User I

Re: Intersect Function

@Keelin 

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:

Year Slicer.JPG

 

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!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors