Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
Solved! Go to 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
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
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
17 | |
11 | |
5 | |
4 | |
3 |