Maximum occuring unique value in a column

[madhav2020]

how to write a query to get maximum occuring unique value and its count in a column ?

Manager

Sujith

Ajay

Joseph

Sujith

Sujith

In the above table i would like to see answer as sujit-3 as it occurs maximum times

Quick help would be highly appretiated

[Smauro]

Hi @madhav2020 

You could try adding a function and calling it based on your column:

    MaxCount = (l as list) =>
        let
            ToTable = Table.FromList(l, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            Group = Table.Group(ToTable, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
            Top = let n = List.Max( Group[Count]) in Table.CombineColumns(Table.TransformColumnTypes(Table.SelectRows(Group, each [Count] = n), {{"Count", type text}}),{"Column1", "Count"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")[Merged]
        in
            if List.Count(Top) = 1 then Top{0} else Top,
    ManagersMax = MaxCount(PreviousStep[Manager])

Where PreviousStep is your previous step.

Note that it will return a text value if the top count of manager is unique, else it will return a list.

Cheers

[madhav2020]

@Smauro Thanks for your effort but i was able to get an answer with the below DAX query which seems to be simple. But thanks again for your effort. Measure 2 = VAR t_ = ADDCOLUMNS(DISTINCT(Table2[Manager]), "Count_", CALCULATE(COUNT(Table2[Manager]))) VAR max_ = MAXX(t_,[Count_]) RETURN CONCATENATEX(FILTER(t_, [Count_] = max_),[Manager]&"-"&[Count_],", ")

[AlB · Accepted solution]

Hi @madhav2020 

if you prefer to do it in DAX, you can create a  measure and place it in a card visual:

Measure 2 = 
VAR t_ = ADDCOLUMNS(DISTINCT(Table2[Manager]), "Count_", CALCULATE(COUNT(Table2[Manager])))
VAR max_ = MAXX(t_,[Count_])
RETURN 
CONCATENATEX(FILTER(t_, [Count_] = max_),[Manager]&"-"&[Count_],", ") 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[madhav2020]

@AIB Thanks for the DAX query. It worked for my case. 🙂