Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.