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

Reply
Highlighted
Orange Belt

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?


Accepted Solutions
Major Blue Belt

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

@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. 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Orange Belt

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

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. 

Major Blue Belt

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

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. 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Major Brown Belt

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

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 



**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Black Belt

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

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?


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Orange Belt

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

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 

 

 

 

Major Blue Belt

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

@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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Major Brown Belt

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

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

 



**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Orange Belt

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

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)

Orange Belt

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

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

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!