Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Greetings,
I was wondering if there was a way to display the most common value in a column? For example:
Reported_Users
UserA
UserB
UserC
UserA
UserA
UserA
Here UserA is the most common user. So I was thinking of trying to display it with a "Card" visual.
I think I read somewhere it's possible with DAX? I'm open to any other methods, if any.
Thank you.
P.S: I'm relatively new to the program.
Solved! Go to Solution.
Hi Daemetius.
So there's a way to do this using Top N, First Non Blank, and Rank X.
The first expression would be to generate a row count from the table that has that data:
User Count = COUNTROWS( TableName )
Then to return a text value for a card you can use this formula below. This ranks the users by row count and returns the top row.
Top User = FIRSTNONBLANK ( TOPN ( 1, VALUES ( UserTableName[UserColumnName] ), RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC) ), 1 )
More info about this can be found on this SQLBI Post. 🙂
Reid Havens - Owner
Hi Daemetius.
So there's a way to do this using Top N, First Non Blank, and Rank X.
The first expression would be to generate a row count from the table that has that data:
User Count = COUNTROWS( TableName )
Then to return a text value for a card you can use this formula below. This ranks the users by row count and returns the top row.
Top User = FIRSTNONBLANK ( TOPN ( 1, VALUES ( UserTableName[UserColumnName] ), RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC) ), 1 )
More info about this can be found on this SQLBI Post. 🙂
Reid Havens - Owner
Hi @Reid_Havens,
Thanks for your response. In my case, User A and User B have the same occurence, but they are from different departments (Sales and Audit, respectively). When I used your solution and use card visual, it displays User A (is it because of alphabetical order?). Then, when I apply "Audit" filter, it still displays User A. What am I missing?
Thanks!
Hi,
Your solution worked for finding the most common column. I have a requirement where I need to display the 3 most common values, in different columns. ie, by creating a measure for the 2nd and 3rd common value. Any suggestions on this?
The problem I face is when the first value was blank, the same value repeats when I change the number for TOPN.
Top_First ActionItem = CALCULATE(FIRSTNONBLANK(
TOPN(
1,
(VALUES(DB_CX_2018_V6_V2_New[Action Items])),
RANKX(ALL(DB_CX_2018_V6_V2_New[Action Items]),[Count_Action Items],,ASC)),1),
FILTER(DB_CX_2018_V6_V2_New,DB_CX_2018_V6_V2_New[Survey_Year]="2019"))
@Reid_Havens wrote:Hi Daemetius.
So there's a way to do this using Top N, First Non Blank, and Rank X.
The first expression would be to generate a row count from the table that has that data:
User Count = COUNTROWS( TableName )Then to return a text value for a card you can use this formula below. This ranks the users by row count and returns the top row.
Top User = FIRSTNONBLANK ( TOPN ( 1, VALUES ( UserTableName[UserColumnName] ), RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC) ), 1 )More info about this can be found on this SQLBI Post. 🙂
Reid Havens - Owner
What is the [Row Count] for? I'm trying to implement this but I get the red underline error on:
RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC)
System is stating that it can't find it or may not be used in this expression
Apologies, forgot to update the name in that one. [Row Count] is supposed to point to the [User Count] Measure. Just update that and you should be good.
Sorry, but when I introduce the code, the equivalent for [User Count] gets underlained in red and tells me that "Argument '3' in ALL function is required'. Which could be the error here?:
1. M.PST = FIRSTNONBLANK( TOPN( 1; VALUES('Datos Numericos'[1.PST Compromiso]); RANKX(ALL('Datos Numericos'[1.PST Compromiso];[Nº Filas];;ASC) ); 1 )
Where [Nº Filas] = COUNTROWS('Datos Numericos')
How can this be done without using measures?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |