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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

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