topic Re: Dax Query - I am unable to put 'OVER' clause over 2 columns using ALLExcept in DAX Commands and Tips

Dax Query - I am unable to put 'OVER' clause over 2 columns using ALLExcept

dixityashovardh — Thu, 05 Aug 2021 03:09:57 GMT

I have one requirement where I need to put OVER (as in SQL) on 2 columns. I am able to achieve it with one column(Subject) using ALLExcept but when I am partitioning data over 2 columns (Subject +part) it is not giving me correct values. Green Numbers indicate what I am expecting at those places.

M_minMarksBySubject = CALCULATE(MIN(SampleData1[Marks]),ALLEXCEPT(SampleData1,SampleData1[Subject])) --> Working

M_minMarksBySubjectPart = CALCULATE(MIN(SampleData1[Marks]),ALLEXCEPT(SampleData1,SampleData1[Subject],SampleData1[Part])) --> Gives same result as previous one

Re: Dax Query - I am unable to put 'OVER' clause over 2 columns using ALLExcept

amitchandak — Thu, 05 Aug 2021 05:47:17 GMT

dixityashovardh , This seem correct , unless there more data then what we see

May be you need max

CALCULATE(MAx(SampleData1[Marks]),ALLEXCEPT(SampleData1,SampleData1[Subject],SampleData1[Part]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Dax Query - I am unable to put 'OVER' clause over 2 columns using ALLExcept

Jos_Woolley — Thu, 05 Aug 2021 06:44:46 GMT

Hi,

It appears that your issue is related to the 'granularity' of your data. I don't fully understand the explanation given here

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

though it appears that the alternative recommended set-up with ALL and VALUES, i.e.:

M_minMarksByPart = CALCULATE ( MIN ( SampleData1[Marks] ), ALL ( SampleData1 ), VALUES ( SampleData1[Part] ) )

will work in your case.

Regards

Re: Dax Query - I am unable to put 'OVER' clause over 2 columns using ALLExcept

dixityashovardh — Thu, 05 Aug 2021 18:54:11 GMT

Input Data :

Sno	Name	Subject	Part	Marks
1	YD	Math	1	59
2	YD	Science	1	88
3	AZ	Design	1	33
4	AZ	History	2	48
5	AZ	Math	1	67
6	CT	Math	1	99
7	CT	Science	2	54
8	CT	Geo	1	28
9	CT	History	2	34
10	MI	Math	2	65
11	MI	Science	2	53
12	QT	Geo	1	46
13	QT	Science	2	24
14	QT	History	1	61

M_minMarksBySubject = CALCULATE(MIN(SampleData1[Marks]),ALLEXCEPT(SampleData1,SampleData1[Subject])) --> Working

M_minMarksBySubjectPart = CALCULATE(MIN(SampleData1[Marks]),ALLEXCEPT(SampleData1,SampleData1[Subject],SampleData1[Part])) --> Gives same result as previous one

I am getting this as output :

Name	Subject	Part	Marks	M_minMarksByName	M_minMarksBySubject	M_minMarksBySubjectPart
AZ	Design	1	33	33	33	33
CT	Geo	1	28	28	28	28
QT	Geo	1	46	24	28	28
AZ	History	2	48	33	34	34
CT	History	2	34	28	34	34
QT	History	1	61	24	34	34
AZ	Math	1	67	33	59	59
CT	Math	1	99	28	59	59
MI	Math	2	65	53	59	59
YD	Math	1	59	59	59	59
CT	Science	2	54	28	24	24
MI	Science	2	53	53	24	24
QT	Science	2	24	24	24	24
YD	Science	1	88	59	24	24

My DESIRED OUTPUT is this :

Name	Subject	Part	Marks	M_minMarksByName	M_minMarksBySubject	M_minMarksBySubject+Part
AZ	Design	1	33	33	33	33
CT	Geo	1	28	28	28	28
QT	Geo	1	46	24	28	28
AZ	History	2	48	33	34	34
CT	History	2	34	28	34	34
QT	History	1	61	24	34	61
AZ	Math	1	67	33	59	59
CT	Math	1	99	28	59	59
MI	Math	2	65	53	59	65
YD	Math	1	59	59	59	59
CT	Science	2	54	28	24	24
MI	Science	2	53	53	24	24
QT	Science	2	24	24	24	24
YD	Science	1	88	59	24	88

Re: Dax Query - I am unable to put 'OVER' clause over 2 columns using ALLExcept

dixityashovardh — Tue, 10 Aug 2021 21:41:37 GMT

I have provided the required sample data.