Get the item with the highest count

Question

Can you please help me to get the item with the highest count using DAX?

Measure = FIRSTNONBLANK('Table1'[ItemName],CALCULATE(COUNT('Table2'[Instance])))

This shows the First ItemName in the table but doesnt get the ItemName of the Highest Value.

Thanks

Nevermind I Already solved it – johndel orolfo Mar 12 '20 at 07:57 — johndel orolfo, Mar 12 '20 at 07:57
How did you do it, John? Don't leave us in suspense. – Ryan B. Mar 12 '20 at 16:27 — Ryan B., Mar 12 '20 at 16:27

Ryan B. · Answer 1 · 2020-03-11T21:07:29.473

Well, it's more complicated than I would have wanted, but here's what I came up with.

There things that you are hoping to do that are not so straightforward in DAX. First, you want an aggregated aggregation ;) -- in this case, the Max of a Count. The second thing is that you want to use a value from one column that you identify by what's in another column. That's row-based thinking and DAX prefers column-based thinking.

So, to do the aggregate of aggregates, we just have to slog through it. SUMMARIZE gives us counts of items. Max and Rank functions could help us find the biggest count, but wouldn't be so useful for getting Item Name. TOP N gives us the whole row where our count is the biggest.

But now we need to get our ItemName out of the row, so SELECTCOLUMNS lets us pick the field to work with. Finally, we really want a value not a 1-column, 1-row table. So FirstNonBlank finishes the job.

Hope it helps.

Here's my DAX

MostFrequentItem = 
VAR SummaryTable = SUMMARIZE ( 'Table', 'Table'[ItemName], "CountsByItem", COUNT ( 'Table'[ItemName] ) )
VAR TopSummaryItemRow = TOPN(1, SummaryTable, [CountsByItem], DESC)
VAR TopItem = SELECTCOLUMNS (TopSummaryItemRow, "TopItemName", [ItemName])

RETURN FIRSTNONBLANK (TopItem, [TopItemName])

Here's the DAX without using variables (not tested, sorry. Should be close):

MostFrequentItem_2 =
FIRSTNONBLANK (
    SELECTCOLUMNS (
        TOPN (
            1,
            SUMMARIZE ( 'Table', 'Table'[ItemName], "Count", COUNT ( 'Table'[ItemName] ) ),
            [Count], DESC
        ),
        "ItemName", [ItemName]
    ),
    [ItemName]
)

Here's the mock data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNSspJTS/NVYrVIZ/nnFmUnJOKznRJzSlJxMlyzi9PSs3JAbODElMyizNQmLEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Stuff = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stuff", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Stuff", "ItemName"}})
in
    #"Renamed Columns"

Get the item with the highest count

1 Answers1