DAX not working.

[SanketBhagwat]

Hi.

I have created 2 measures using same formula, but one of my DAX is not working and other is working fine.

I have attached screenshots of the same.

Highest Qualifications and Attendance are calculated using same formula;
Highest Qualifications = MAX(WC_Years[QualifiedTeams])

Highest Attendance = MAX(WC_Years[Attendance])

I don't know why it is giving an error.

[Fowmy]

@SanketBhagwat 

The 2nd screenshot doesn't show the Qualification measure, you need to clear on it and share the screenshot.

You use ALLSELECTED(WC_Years[Year]) instead of GROUP BY

[SanketBhagwat]

Hi @Fowmy 

ALLSELECTED() too is giving the same error.

[Anonymous]

You are getting this error because the TOPN function is returning multiple values. There are multiple years for which the value is equal to Highest Qualifications. 

[SanketBhagwat]

Hi @Fowmy @Anonymous 

I just noticed that there are multiple year values for which the 'Highest Qualifications' measure is giving the same value.

Do you guys know any DAX which will return only 1 year value.

[Fowmy]

@SanketBhagwat 

You can use MAXX

Attendnace = 
MAXX (
  VALUES(WC_YEARS[YEAR]),
  [Highest Attendance]
)

 Qualification = 
MAXX (
  VALUES(WC_YEARS[YEAR]),
  [Highest Qualification]
)

[SanketBhagwat]

Sorry, but the solution you provided didn't gave the correct answer.

[Fowmy · Accepted solution]

@SanketBhagwat 

So you need the year with the highest value:

Qualification = 

MAXX(
    TOPN (
        1,
        ALLSELECTED (WC_YEARS[YEAR]),
        [Highest Qualification]
    ),
    WC_YEARS[YEAR]
)