Hi, I would like to find the most frequenct value(most repeated times) by DAX. May I know is it possible? Original TableItemIDuser11Peter12Peter13Alex14Peter15Chris16Chris21Alex22Alex23Peter Desired Result:ItemMost Frequency1Peter2Alex

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.

DAX - Most Frequent value | Microsoft Fabric Community

22 Replies

Ashish_Mathur
Super User
5 years ago
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.
- ngct1112
  Post Patron
  5 years ago
  Ashish_Mathur Thanks Ashish, it works fine in my model. Appreciated.
  - Ashish_Mathur
    Super User
    5 years ago
    You are welcome. If my previous reply helped, please mark that as Answer.
- ngct1112
  Post Patron
  5 years ago
  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
    Super User
    5 years ago
    Hi,
    
    This measure works
    
    Most frequent = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count],DESC),1),Data[group]="B")
    
    Hope this helps.
- Khaled2023
  Frequent Visitor
  2 years ago
  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
    Super User
    2 years ago
    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.
Jihwan_Kim
Super User
5 years ago

Most Frequent User : =
VAR _userstable =
RELATEDTABLE ( Data )
VAR _groupbyusers =
GROUPBY ( _userstable, Users[user], "@count", SUMX ( CURRENTGROUP (), 1 ) )
VAR _maxcount =
MAXX ( _groupbyusers, [@count] )
VAR _maxcountuserlist =
SUMMARIZE ( FILTER ( _groupbyusers, [@count] = _maxcount ), Users[user] )
RETURN
IF (
HASONEVALUE ( Items[Item] ),
IF ( COUNTROWS ( _maxcountuserlist ) = 1, _maxcountuserlist )
)

Link to the pbix file
- ngct1112
  Post Patron
  5 years ago
  Jihwan_Kim Thanks for your solutions!