Forum Discussion

amvans_90's avatar
amvans_90
Regular Visitor
1 year ago
Solved

Get MAX value for multiple categories

Trying to get the most recent phone number created for each office.  I know it needs to be a MAX Phone # Generated ID" for each office, but I can't figure out the DAX to do it. This is a sample:

And this is what I'm trying to get to:

 

Need Help

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi amvans_90 ,

     

    try to plot the table visual with office column and two measures like:

    Max Phone# =MAX(tablename[Phone #]

    MAX ID =MAX(tablename[Phone # Generated ID]

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Office", type text}, {"Phone #", type text}, {"Phone # Generated ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Office"}, {{"Count", each Table.Max(_,"Phone # Generated ID")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Phone #", "Phone # Generated ID"}, {"Phone #", "Phone # Generated ID"})
in
    #"Expanded Count"

    hope this helps.