Dax to Return Most Common Value out of Combinations.....

[DW868990]

Hi,

I am looking for the correct DAX logic for the below situation, I have a table like below:

I am looking for the DAX calculation to dynamically calculate the overall rating for each customer, based on the result in the 5 events. So for the 'Overall' to be the most common answer, however on cases were the 2 answers are tied and there is no clear most common value for the Overall value to be the highest rating.

Ie - customer has 2 awful ratings, 2 amazing, 1 poor - So the overall rating would be amazing.

Thanks in advance.

Dave

[Greg_Deckler · Accepted solution]

@DW868990 A few problems with this. What defines better rating? I guess you could hard code it. Also, is that the way your data looks? It would be a lot easier if you unpivoted the Event columns, otherwise it will be sadness to code. If you unpivot then it will look something like:

Overall Measure =
  VAR __Table = SUMMARIZE('Table',[Event Rating],"__Count",COUNTROWS('Table')
  VAR __Max = MAXX(__Table,[__Count])
  VAR __Table1 = FILTER(__Table, [__Count] = __Max)
  VAR __CountRows = COUNTROWS(__Table1)
  VAR __Rating = MAXX(__Table1,[Event Rating])
RETURN
  IF(
    __CountRows = 1, 
    __Rating,
    SWITCH(TRUE(),
      "Amazing" IN SELECTCOLUMNS(__Table1,"Rating",[Event Rating]),"Amazing",
      "Okay" IN SELECTCOLUMNS(__Table1,"Rating",[Event Rating]),"Okay",
      "Poor" IN SELECTCOLUMNS(__Table1,"Rating",[Event Rating]),"Poor",
      "Awful"
    )
  )
      

[DW868990]

ah yes, can unpivot column no bother and this should do the trick! thank you 🙂