topic Re: Dynamic filtering/column issue in DAX Commands and Tips

Dynamic filtering/column issue

Anonymous — Thu, 30 Jul 2020 08:19:56 GMT

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

Re: Dynamic filtering/column issue

amitchandak — Thu, 30 Jul 2020 01:51:54 GMT

Anonymous , I do not see any qtr in data.

Typically when I do not have the date, Try to add a date and try to use date table and time intelligence. Else I move time-related data to a new table. Create a rank and do it.

Using Rank

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

uning Time Intelligence 2
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

Creating date when Date is not there

Within first few mins -https://www.youtube.com/watch?v=yPQ9UV37LOU&t=28s

Re: Dynamic filtering/column issue

Anonymous — Thu, 30 Jul 2020 06:01:29 GMT

Hi Anonymous ,

Can you share sample data aong with dates.

Regards,

Harsh Nathani

Re: Dynamic filtering/column issue

Anonymous — Thu, 30 Jul 2020 08:17:14 GMT

Sorry, I should have been clearer. Q1 and Q3 are quartile 1 and quartile 3, not quarter 1 and quarter 3. This is an attempt to identify "outliers" in exam results data, the only time variable is year.

So Q3 = percentile.inc([Average Mark], 0.75) for example.

I will re-edit original post for clarity.

Re: Dynamic filtering/column issue

Anonymous — Thu, 30 Jul 2020 08:50:56 GMT

Hi Anonymous ,

See if these help

https://community.powerbi.com/t5/Desktop/Dynamic-Quartiles/m-p/814004

https://community.powerbi.com/t5/Desktop/Salesperson-production-in-quartiles-by-region-dynamic/m-p/541186

https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/

https://community.powerbi.com/t5/Desktop/quartile-ranking/td-p/560850

Regards,

Harsh Nathani

Re: Dynamic filtering/column issue

Anonymous — Thu, 30 Jul 2020 10:07:54 GMT

Definitely, thanks very much