cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daemetius
Helper II
Helper II

How to obtain the most common value from a column and display it?

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.

1 ACCEPTED 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

Havens Consulting Inc.

View solution in original post

7 REPLIES 7

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

Havens Consulting Inc.

Anonymous
Not applicable

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

Havens Consulting Inc.



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?

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors