DAX - Most Frequent value

[ngct1112]

Hi,

I would like to find the most frequenct value(most repeated times) by DAX. May I know is it possible?

Original Table

Item	ID	user
1	1	Peter
1	2	Peter
1	3	Alex
1	4	Peter
1	5	Chris
1	6	Chris
2	1	Alex
2	2	Alex
2	3	Peter

Desired Result:

Item	Most Frequency
1	Peter
2	Alex

[Ashish_Mathur · Accepted solution]

Hi,

To your Table visual, drag the Item field and write these mesures

User count = counta(Data[User])

Most frequency = FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count]),1)

Drag the second measure to your visual.

[Anonymous]

Excellent solution! But can it easily be turned into Custom Columns instead of Measures? The reason I ask is that I have implemented your solution for two different fields and would like to be able to collapse and expand for each of them.

[Ashish_Mathur]

Thank you.  I cannot understand why you want to convert a measure into a calculated column formula.

[Anonymous]

I have broadcasting data where each program and each episode has a unique ID. There are however more than one program- and episode name behind each ID. Ideally, I would like to have a Matrix visual where I could expand or collapse a selected program or episode - using the most frequent name as a proxy and saving space by not showing the IDs. The problem with Measures is that they can't be added as rows (which is the prerequisite for expanding/collapsing) - they can only be added under Values:

[Ashish_Mathur]

I do not understand.  Someone who does will help you.

[Khaled2023]

Hi,

How can I get the number (how many times repeated the most frequent value).

I'd like to display it in a card.

Thanks

[Ashish_Mathur]

Hi,

Write the formula suggested in my message.  If t does not work, then share some data to work with, explain the question and show the expected result.

[Khaled2023]

Hi, apprecaite your feedback. 

It works and give me the value "Name of most frequent client", but I need how many time this client name repeated, for example 10 times. I need it as measure not column nor table.

Thanks

[Ashish_Mathur]

Hi,

Drag client to the visual and write this measure

Count = countrows(Data)

Hope this helps.

[ngct1112]

Hi @Ashish_Mathur , may I have a futher question regarding this formula?
Is it possible I could add a filter in your formula like filter "group" = "B"

Appreciated if you could help

Original:

Item	ID	user	group
1	1	Peter	A
1	2	Peter	A
1	3	Alex	A
1	4	Peter	B
1	5	Chris	B
1	6	Chris	B
2	1	Alex	A
2	2	Alex	A
2	3	Peter	B

Desired result:

Item	Mast Frequent(B)
1	Chris
2	Peter

[Ashish_Mathur]

Hi,

This measure works

Most frequent = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count],DESC),1),Data[group]="B")

Hope this helps.

[ngct1112]

@Ashish_Mathur I am really sorry forget not to mention that "group" itself is a measure.

Its shows the error below when I use the CALAULATE:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 Is there any way to deal with this case. Appreciated!

[Ashish_Mathur]

Share the link from where i can download your PBI file with your measures already written there.

[ngct1112]

@Ashish_Mathur Please see the Sample BI 

I am hoping I could get the sub-total "supplier" as the most frequently shown supplier in the group"A" rather than a MAX value.

Appreciated!

[Ashish_Mathur]

I am confused.  In the Group column, you do not have any entry as A at all.  Do not be in a hurry to post.  Read your own question multiple times before posting.  What exact result do you expect to see in the sub total row and why?

[ngct1112]

I am hoping I could show the users, for each QTY,

1.)which supplier with the lowest cost & the cost (has been achieved)

2.) Before Expanding the QTY, could it show the most frequenct supplier from all the QTY.

Like your provided measure, except the filter component is a Measure rather a column now. 

**Filter "Rank by Supplier" = 1

Supplier 1 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Table[Group]),[User count],DESC),1),[Rank by Supplier]=1)

For this case, the supplier 1's result should be "A" since "A" is the most frequent supplier in all QTY

The reason of doing this is letting the user know generally, which supplier should be used since that supplier with the lowest cost for most cases.

May I know is it achievable? 

[Ashish_Mathur]

I still do not understand your expected result.  Someone else who does will help you.

[ngct1112]

@Ashish_Mathur No worries. You did help a lot. Great thanks for that.

[ngct1112]

@Ashish_Mathur Thanks Ashish, it works fine in my model. Appreciated.

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that as Answer.