Power BI - Filter to count Number for Max Count

Question

I have the issue I can not go about. I want to use Card to count Number but only for max value of Count. So in my example it will be 5 not 9.

Number  Count
1       100
2       101
3       102
3       103
3       104
3       105
4       106
5       107
5       108

What do you mean by `count Number for max value of Count`. Do you want count distinct Number? Why 5? Because there are 5 distinct values in column Number? Or maybe you select MAX(Count) = 108 (one row) and you then you check in Number column what is value for that - here it is also 5. — Przemyslaw Remin, Aug 10 '18 at 09:25
Ok, I understand that your `either way` means either M or DAX. Please explain the logic you want to get your desired results. See my second comment. Why have you chosen `5 not 9`? — Przemyslaw Remin, Aug 10 '18 at 12:58

Alexis Olson · Answer 1 · 2018-08-10T13:51:06.883

You can do this in several steps.

Calculate the maximum Count
Find which Number values correspond to this max Count
Count the occurrences of these Number values

Here's one example of a measure that does this:

Count of Max = 
    VAR MaxCount = CALCULATE(MAX(Table1[Count]), ALLSELECTED(Table1))
    VAR MaxNumbers = CALCULATETABLE(VALUES(Table1[Number]), Table1[Count] = MaxCount)
    RETURN CALCULATE(COUNT(Table1[Number]), Table1[Number] IN MaxNumbers)

The last line can be written in many different other ways. For example,

COUNTROWS(FILTER(Table1, Table1[Number] IN MaxNumbers))

or

SUMX(Table1, 1*(Table1[Number] IN MaxNumbers))

Edit: From your comment, it looks like you don't want to do the third step mentioned above. In this case, you only have two steps.

Number for Max Count = 
    VAR MaxCount = CALCULATE(MAX(Table1[Count]), ALLSELECTED(Table1))
    RETURN CALCULATE(MAX(Table1[Number]), Table1[Count] = MaxCount)

If there are multiple Number values associated with the maximal Count value, then this returns the max of those associated values.

@ Alexis Olson. It gives me zeros for all numbers except 5 (where it gave 1 or 2 depending on the code). What I am after is the following: Number / Desired Count: 1 / 100; 2/101; 3/105; 4/106; 5/108 — Kalenji, Aug 10 '18 at 09:45
Oh, it looks like you don't actually want to do the third step. I read your title as the count of the max number, not just that number. Please see my edit. — Alexis Olson, Aug 10 '18 at 13:48

Przemyslaw Remin · Answer 2 · 2018-08-10T13:43:38.773

Here is one of the possible ways to do it in M. Paste this code in advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tca5DQAwCASwXa6m4E2yC2L/NSJdhSt3wyAwVYw0nDc+eF+P9VwvPvnDF3/XH2Y+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Count", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "IsMax", each [Count] = List.Max(#"Changed Type"[Count])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsMax] = true))
in
    #"Filtered Rows"

Here crucial is the third step where you add custom column IsMax which checks if it is max of Count column:

Alternative for selecting MAX value per category, you may find here: Select row with MAX value per category Power BI

And here is even more generalized approach with adding index per category: Index by category in Power BI equivalent to SQL row_number over partition

Update. If you want max value per category - this is solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tca5DQAwCASwXa6m4E2yC2L/NSJdhSt3wyAwVYw0nDc+eF+P9VwvPvnDF3/XH2Y+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {{"Grouped_by_Number", each List.Max([Count]), type number}})
in
    #"Grouped Rows"

Thanks, it just gives TRUE for the Max Number and Count (ie. 5 and 108. It does not work for Number 3. — Kalenji, Aug 10 '18 at 13:29
So your `Count` column is a Category column. And you want to select max value of Count for each category? Is that right? — Przemyslaw Remin, Aug 10 '18 at 13:39
See update of my answer. If that is not it, please paste in your question full desired results, including no.5 no. 3 and other numbers. It stems out from your question that only 5 is the number you want to get. — Przemyslaw Remin, Aug 10 '18 at 13:46

Power BI - Filter to count Number for Max Count

2 Answers2