How to select a descriptive value based on MAX value of another column (Beast Mode)

I have a two columns. One contains Marks, and the other contains Names of students. I want to return the name of the student who has highest marks (I want to use this name in a card). 

How do I do this in the Beast Mode?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    Accepted Answer

    @hamza_123 

    My suggestion would be to create beast mode field called image url that just looks for the person's name and then build the proper HTML to display the picture like this:

    CASE
    WHEN `Student_Name` = 'John'
    THEN CONCAT('<div style="text-align:center"><img alt="DOWN" height="200" src="https://studentJohn.jpg">','</div>')

    WHEN `Student_Name` = 'Betty'
    THEN CONCAT('<div style="text-align:center"><img alt="DOWN" height="200" src="https://studentBetty.jpg">','</div>')

    ELSE ''
    END

    Then, just drag the student name, the image url and the Marks column into the table card columns and click on the Marks column and choose MAX for the calculation type. This will show you the name, their highest score and their picture. You can then add Marks to the sorting and sort by descending and then change all rows to 3 in the data table properties, for example, if you just wanted to show the top 3 people. 

Answers

  • I used to work on PowerBI before. The function for this task used to be MAXX. It calculates the maximum of one column based on another. 

    Also wondering in there's a function like this in the Beast Mode. 

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Assuming your MAX value is numeric, I don't think you even need to use a beast mode for this. (Although, there is MAX function in the beast mode.) For example, start with a table card and then drag your student name in as one column and then drag your Marks column as another column. Click on that column and choose Maximum from the calculation dropdown list. This should give you the highest value for each person. 

  • Depending on your data @MarkSnodgrass is correct, can you provide some information about your dataset if you don't get it working? We would be able to tell you exactly what to do 

  • I'm not sure that this is what @hamza_123 is after here.  It sounds to me like he wants to create a card that will provide the name of the student that received the highest score.  I suppose if you sorted the card by max(`Score`) then you would see the student name in the first bar or line of data.  You could even then click on Data Table and set the card to only display the 1st row of data.  Like @guitarhero23 mentioned though, it's dificult to say for sure without some more input from hamza.

     

    Hamza, could you share some sample data and maybe a screenshot of what you are looking for?

  • So lets say I have two columns:

     

    Student: Marks 

    John : 98

    Mary : 70 

    John : 90 

    Mary : 60 

     

    I want to find out the name of student who has the highest total marks (in this case, it is John). 

    If I have the name of John, I can put it in a card (which is what I want). 

     

    a) Could anybody tell be any beast mode calculation using which I can find out what I need? (The earlier suggested MAX in table does not work for me. I do not want a table)

     

    Since I failed in doing the above, I made another table (using a SQL transformation) which grouped all students. So Now I have:

     

    Student: Marks 

    John : 188

    Mary : 130 

     

    Now, I use the Beast Mode:

    CASE
    WHEN `Marks` = MAX(`Marks`) THEN `Student`
    ELSE 0
    END

     

    Surprisingly, this beast mode calculation is also giving me 0 as a result (it should give me John). I cannot understand why. 

    Help will be appreciated 

     

     

     

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    @hamza_123  What card do you want to use to display this information? The table card was just a suggestion so you can easily verify the results. The MAX function should still be available as a calculation type in other card types that you can choose from.

  • The beastmode is an issue because you're telling you you want a string returned if if meets your WHEN criteria (student name) but if it doesn't return a number.

     

    So depending on the type of card you want how to achieve it may be different.

     

    For example, if you were to just use a textbox card you can achieve your result without a beastmode

    DojoHelp100.JPG

     

     

    If you're looking for only showing the top person you can do an ETL to Rank all the scores from 1 - X then you can just filter to show the top 1 record.

     

    Instructions for that are here https://dojo.domo.com/t5/Card-Building/Ranking-Bar-Chart-Limitation/m-p/44085#M6040 just switch it to be relevant for your columns

     

    If all else fails I'm sure @ST_-Superman-_ can conjure up some wizardry of a beast mode to achieve the same

     

  • I'm trying to put a picture in a table card, of the student who scores the highest marks. 

     

    My code looks like this:

     

    CASE
    WHEN ( CASE
    WHEN `Marks` = MAX(`Marks`) THEN `Student_Name`
    ELSE 0
    END) = 'John'
    THEN CONCAT('<div style="text-align:center"><img alt="DOWN" height="200" src="https://student.jpg">','</div>')

    ELSE 0
    END

     

    This is only a small part of the code. In reality, I will have links of pictures of all students in the card (with more WHEN statements)

     

    (I'd prefer a Beast Mode solution to an ETL solution)

  • Also @guitarhero23  even if I change my WHEN condition to return texts (with the ELSE), still gives me the else text. 

  • The issue with this beast mode is that you are trying to aggregate the data while also not aggregating the data.  Let me try to explain...

    If you have a list of scores and names:

    Clark Kent = 100

    Bruce Wayne = 98

    Peter Parker = 97

    Bruce Banner = 85

    Berry Allen = 92

    **bleep** Grayson = 81

    Tony Stark = 94

    Steve Rogers = 89

    Reed Richards = 79

     

    With your beastmode, you are trying to evaluate when a single score is equal to the max of the score field.  However, the way SQL evaluates that statement is to look at each row and evaluate it.  In this case, it would look at the row for Clark Kent and see that `score` = 100 and then look for max(`score`) which is 100 and find a match.  Then it will look at the next row and see Bruce Wayne's score of 98.  When it evaluates if 98 is equal to the max value for score, since it is now only looking at the row for Bruce, it will determine that 98 is the max value.  In other words, because you are wanting to evaluate a single row of data at a time, every value for `score` is a "max" value for that row.

     

    The solution is to calculate the max value in the dataflow.

     

    If you could post a sample of your dataset (feel free to randomize the names or any other data you feel is sensitive) as well as what the entire code would really look like.  (if you are just creating a separate "when" statement for each student, then don't worry about sending the code)

     

    If I'm understanding your use case here, you are going to have a table card with a list of student's marks and you want to display the picture of the student with the highest marks.  Is that correct?

     

    I built out a different solution yesterday but did not have time to post it...

     let me know if this would meet your needs1.png

     

  • This is the best solution. Could you please tell how to limit the row to only one? (I only want to show the top student's picture). 

  • thankyou @ST_-Superman-_ . I also grouped the table through a SQL transformation, but there is still the same issue with the BEAST mode (even with only one student per row). 

     

    Let me know if you propose anything else. Appreciate your support

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Glad that works for you. To limit to 1 is really easy. 

    1) Add your Marks column to the Sorting section and sort by Descending.

    2) Go the data table properties and change All Rows to 1 by typing 1 in there. (If you don't see your data table below your chart properties, make sure Data Table is selected in the navigation menu.

    nav.PNG

     

    datatable.PNG

    Click where it says All rows and type a 1.

    This will make your card show only 1 row.