Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Specifying MODE (or a user-defined aggregate function) when using DAX SUMMARIZE

I cannot find a MODE function in DAX. I need to use it in conjunction with SUMMARISE

I don't want to do it in Power M Query (though have done successfully in the past)
And I cannot find a way of writing a custom user-defined function.
 
Is MODE in the plan for Power BI? Or being able to write custom user-defined functions?
 
Mike
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous  ,

    This is the data I created. I define the rules to calculate the most frequent string of name:

    I learned that the mode expression is the most frequently displayed value, and you can use this method instead:

    1. Create Calculated column.

    Column =
VAR summary =
     SUMMARIZE (
        'Table',
        'Table'[name],
        "Count",COUNTX(FILTER('Table','Table'[name]=EARLIER('Table'[name])),'Table'[name])
    )   
    VAR _max =
    MAXX ( summary, [Count])
RETURN
    MAXX ( FILTER ( summary, [Count] = _max ), [name] )

    2. Result：

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you. I saw that in my research but can't work out how to use the formula. Does it define a MODE function? Or somehow add a column?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you. I did get this working.

      Though I still think that MODE should be offered as a standard aggregate function

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous  ,

    This is the data I created. I define the rules to calculate the most frequent string of name:

    I learned that the mode expression is the most frequently displayed value, and you can use this method instead:

    1. Create Calculated column.

    Column =
VAR summary =
     SUMMARIZE (
        'Table',
        'Table'[name],
        "Count",COUNTX(FILTER('Table','Table'[name]=EARLIER('Table'[name])),'Table'[name])
    )   
    VAR _max =
    MAXX ( summary, [Count])
RETURN
    MAXX ( FILTER ( summary, [Count] = _max ), [name] )

    2. Result：

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you. I can see this works.

      I still think Power BI would be better if it had the following aggregate functions:

      1) MODE (as in this problem)

      2) Concatenate all unique strings, sorted. This is often useful to aggregate text strings