Forum Discussion

ngct1112's avatar
ngct1112
Post Patron
5 years ago
Solved

DAX - Most Frequent value

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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    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.

Ashish_Mathur's avatar
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.

ngct1112's avatar
ngct1112
Post Patron
5 years ago

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's avatar
    Ashish_Mathur
    Super User
    5 years ago

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

    • ngct1112's avatar
      ngct1112
      Post Patron
      5 years ago

      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's avatar
        Ashish_Mathur
        Super User
        5 years ago

        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?