Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to 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!)
Proud to be a Super User!
Paul on Linkedin.
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?
Proud to be a Super User!
Paul on Linkedin.
@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!)
Proud to be a Super User!
Paul on Linkedin.
@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.
@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.
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |