Max Value based on two categories

[alanbbarton]

Need help trying to get a number based on the year and the week.  I cannot just create a new measure in the original query but need to create a new measure in the visulization itself (data is detail level and the field I am reporting on is repeated many times - thus he max will get what I need).

I have tried the following but cannot figure out how to get the second criteria.

Max of Westpack max per Year =

MAXX(

    KEEPFILTERS(VALUES('ExternalNumbers'[Year])),

    CALCULATE(COUNTA('ExternalNumbers'[Westpack]))

)

I need  to also filter by 'ExternalNumbers'[Week_Number].

FOR EXAMPLE

Employee Code	Hours	Year	Week	WestPac
12324	8	2022	1	18
35465	7	2022	1	18
65487	6	2022	1	18
12324	5	2022	2	20

What I need it to tell me that for Year 2022 Week 1 its 18 and for Year 2022 Week 2 its 20.  

Hope that helps more!

[alanbbarton]

Almost there but apparently the fields (all three) are text and it must be a number.  Now I tried to change it in the table using Change Type but I am getting other issues.  Is it possible to change the fields to number in DAX?  I tried the following and it failed:

Sumx(summarize(Table, VALUE(Table[Year]) , VALUE(Table[Week]), "_1", Max(VALUE(Table[WestPac]))),[_1])

[amitchandak]

@alanbbarton ,

Sumx(summarize(Table, Table[Year] , Table[Week], "_1", Max(Table[WestPac])),[_1])

or

calculate(Sumx(summarize(Table, Table[Year] , Table[Week], "_1", Max(Table[WestPac])),[_1]), Filter(allselected(Table), Table[Year] = Max(Table[Year])  && Table[Week] = Max(Table[Week])  ) )