**edited to clarify terminology**
Hi,
New to Power Bi and DAX and struggling with the following issue.
I have a very large dataset with properties similar to the below example data. I am trying to identify if my data for the year 2020 is unusual (because of covid). My analysis is being done at Faculty level.
A simplification of what I would like to do is filter my list of subjects from 2020 based on if they are an outlier inside a Faculty based on 2019 data. To make the example simple here I will just call an outlier >Quartile 3 (Q3) or <Quartile 1 (Q1) of the 2019 data.
Calculating Q1 and Q3 for each faculty is easily done with a measure. However I do not want to hard code those values anywhere for a number of reasons.
So what I need in the below table is a calculated column "outlier" that identifies for a subject in 2020 in a particular Faculty if the average mark is >Q3 or <Q1 for the same faculty for 2019 data. But this value needs to be dynamic, so for example if I realise subject C should be in Faculty 1 instead of 2, a recalculation is done to redetermine the outlier status of this subject, etc.
Logically what I need but because of my lack of DAX knowledge cannot construct is:
=IF(OR([Average Mark] > (Dynamic Value of Q3 for Subject's Faculty and Year = 2019), [Average Mark] < (Dynamic Value Q1 for Subject's Faculty and Year = 2019), "Outlier", "Not Outlier")
Any help, hints, directions to similar postings appreciated.
| Subject | Faculty | Year | Average Mark |
| A | 1 | 2020 | 50 |
| B | 1 | 2020 | 60 |
| C | 2 | 2020 | 60 |
| D | 2 | 2020 | 55 |
| E | 3 | 2020 | 65 |
| F | 3 | 2020 | 75 |
| A | 1 | 2019 | 85 |
| B | 1 | 2019 | 55 |
| C | 2 | 2019 | 65 |
| D | 2 | 2019 | 60 |
| E | 3 | 2019 | 35 |
| F | 3 | 2019 | 45 |
