Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi Experts,
Test Data file: PowerBi.Pbix
I have data where the all reasons are in one column and I need to find the TOP 10 along with other columns where I need top 10, SD, mean etc, Reason count by Reason ( is the index created grouped by Reason).
I tried following adapted from the solution in the Community Forum.
1. I need help to get TOP 5 reasons in the column and
2. When I do not have to create the index in the query too, if possible so other measures can be done without adding the steps of Index and group by.
RankReason =
IF (
ISINSCOPE ( 'Data'[ReasonCountByReason]),
RANKX (
CALCULATETABLE (
VALUES ( Data[ReasonCountByReason] ),
ALLSELECTED ( Data[ReasonCountByReason] )
),
[ReasonCountByReason]
),
IF (
ISINSCOPE ( 'Data'[ReasonCountByReason] ),
VAR TotalReasons = Data[TopReasons]
RETURN
CALCULATE (
RANKX (
VALUES ( 'Data'[ReasonCountByReason] ),
[TopReasons],
Data[TopReasons]
),
ALLSELECTED ()
)
)
)
-- Did not work
Solved! Go to Solution.
Hi, @Anonymous
Please try the following methods. You can solve it directly with Dax without creating indexes in the power query.
Measure:
Count name =
CALCULATE (
COUNT ( Data[TotalReasons.Name] ),
FILTER (
ALL ( Data ),
[TotalReasons.Name] = SELECTEDVALUE ( Data[TotalReasons.Name] )
)
)
Count reason =
CALCULATE (
COUNT ( Data[Reason] ),
FILTER ( ALL ( Data ), [Reason] = SELECTEDVALUE ( Data[Reason] ) )
)
Rank name = RANKX(ALLSELECTED(Data[TotalReasons.Name]),[Count name],,DESC)
Top name = IF([Rank name]<=2,1,0)
Put the measure of Top name into the name's view and set it equal to 1.
Is this the result you want for Top 2 name?
Rank reason = RANKX(ALLSELECTED(Data[Reason]),[Count reason],,DESC)
Top reason = IF([Rank reason]<=5,1,0)
Is this the result you want for Top 5 reason?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Please try the following methods. You can solve it directly with Dax without creating indexes in the power query.
Measure:
Count name =
CALCULATE (
COUNT ( Data[TotalReasons.Name] ),
FILTER (
ALL ( Data ),
[TotalReasons.Name] = SELECTEDVALUE ( Data[TotalReasons.Name] )
)
)
Count reason =
CALCULATE (
COUNT ( Data[Reason] ),
FILTER ( ALL ( Data ), [Reason] = SELECTEDVALUE ( Data[Reason] ) )
)
Rank name = RANKX(ALLSELECTED(Data[TotalReasons.Name]),[Count name],,DESC)
Top name = IF([Rank name]<=2,1,0)
Put the measure of Top name into the name's view and set it equal to 1.
Is this the result you want for Top 2 name?
Rank reason = RANKX(ALLSELECTED(Data[Reason]),[Count reason],,DESC)
Top reason = IF([Rank reason]<=5,1,0)
Is this the result you want for Top 5 reason?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
117 | |
84 | |
49 | |
38 | |
28 |
User | Count |
---|---|
185 | |
73 | |
73 | |
50 | |
42 |