Forum Discussion
Intersect Function
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!)
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?
PaulDBrown yes if possible, thanks
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!)
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.