cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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?