Forum Discussion
Solved
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...
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
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
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_],", ")