Forum Discussion
Maximum occuring unique value in a column
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
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
4 Replies
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.
CheersHi 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
- @AIB Thanks for the DAX query. It worked for my case. 🙂
- 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_],", ")
