Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.