cancel
Showing results for
Did you mean:
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
MVP

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

Reid Havens - Owner

Havens Consulting Inc.

7 REPLIES 7
MVP

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

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!

Helper I

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

Helper II

@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
)```

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

MVP

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.

Helper I

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

Regular Visitor

How can this be done without using measures?

Announcements

The Power BI Community Show

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

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