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.

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?

      • ngct1112's avatar
        ngct1112
        Post Patron
        5 years ago

        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?