Forum Discussion

alanbbarton's avatar
alanbbarton
New Member
4 years ago

Max Value based on two categories

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 CodeHoursYearWeekWestPac
1232482022118
35465720221

18

6548762022118
1232452022220

 

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!

DAX
MAXX

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])  ) )

  • alanbbarton's avatar
    alanbbarton
    New Member
    4 years ago

    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])