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

[Anonymous]

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

[amitchandak · Accepted solution]

@Anonymous , refer if old solution can help

https://community.powerbi.com/t5/Desktop/Calculating-modes/m-p/46313

[Anonymous · Accepted solution]

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]

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

[amitchandak · Accepted solution]

@Anonymous , refer if old solution can help

https://community.powerbi.com/t5/Desktop/Calculating-modes/m-p/46313

[Anonymous]

Thank you. I did get this working.

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

[Anonymous]

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?