Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JeanBean
Frequent Visitor

Max Count of Group then Top 5 of Max

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])

 

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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? Smiley Happy

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Employee
Employee

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? Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.