Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dixityashovardh
Frequent Visitor

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

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
 

2021-08-04_22-56-02.png

4 REPLIES 4
Jos_Woolley
Solution Sage
Solution Sage

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

 

 

amitchandak
Super User
Super User

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

I have provided the required sample data.

Input Data :

 

SnoNameSubjectPartMarks
1YDMath159
2YDScience188
3AZDesign133
4AZHistory248
5AZMath167
6CTMath199
7CTScience254
8CTGeo128
9CTHistory234
10MIMath265
11MIScience253
12QTGeo146
13QTScience224
14QTHistory161

 

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 :

NameSubjectPartMarksM_minMarksByNameM_minMarksBySubjectM_minMarksBySubjectPart
AZDesign133333333
CTGeo128282828
QTGeo146242828
AZHistory248333434
CTHistory234283434
QTHistory161243434
AZMath167335959
CTMath199285959
MIMath265535959
YDMath159595959
CTScience254282424
MIScience253532424
QTScience224242424
YDScience188592424

 

My DESIRED OUTPUT is this :

NameSubjectPartMarksM_minMarksByNameM_minMarksBySubjectM_minMarksBySubject+Part
AZDesign133333333
CTGeo128282828
QTGeo146242828
AZHistory248333434
CTHistory234283434
QTHistory161243461
AZMath167335959
CTMath199285959
MIMath265535965
YDMath159595959
CTScience254282424
MIScience253532424
QTScience224242424
YDScience188592488

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors