Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic filtering/column issue

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

 

SubjectFacultyYearAverage Mark
A1202050
B1202060
C2202060
D2202055
E3202065
F3202075
A1201985
B1201955
C2201965
D2201960
E3201935
F3201945

 

5 Replies