Max Count of Group then Top 5 of Max

[JeanBean]

Hi,

I am new to Power Bi and I need help with this. I need to display the top N categories with the max count of that group. I cant use rank because I need to find the max grouped by. I wrote what I need in code and what I hope to display on power BI.

I need it to first give me a total count of all category by open date (inner select), then I need to get the top N categories with the most count (couter select).

Ideal SQL:

select top 5 b.cat, b.max_res
from (
       select a.Cat,  max(result_count) as max_res
       from (
              select Cat, Open_dt, count([result number]) as result_count
              from [result]
              group by Cat, open_dt) a
       group by a.Cat) b

so far I have 

Total_Result = DISTINCTCOUNT([result number])

When ever I use the MAX() I get this argument: The MAX function only accepts a column reference as the argument number 1.

MAX_G = CALCULATE(MAX([Total_Result]), GROUPBY(Query1, Query1[Cat], Query1[Open_dt]))

Then for TopN I get this Issue: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

TOPn = topn(5, Query1, [MAX_G])

[v-ljerr-msft · Accepted solution]

Hi @JeanBean,

Could you post your table structures(including table relationships) with some sample data and your expected result, so that we can better assist on the DAX formula needed? 

Regards