I have an unexpected outcome of a beast mode calculated column, can you help?

Samuel. · 2022-11-24T18:24:37+00:00

There was an error rendering this rich post.

Hey,

I've been working on a dashboard that has a radio selector to select which project the dashboard presents data from.

In this process, if an option is not set on the radio selector, my dashboard populates with "Please filter on Project" on most of my cards except one which is not behaving as expected.

For context, each project has a name, manager and sponsor; with my in-card filters, there are nine projects.

The code below works as intended

CASE
WHEN COUNT(`Project_Manager`) = 1 
THEN CONCAT('Delivery Lead: ',`Project_Manager`,'
', 'Accountable Executive: ', `Project_Sponsor`) 
ELSE 'Please Filter on Project' 
END

However, as soon as I add more to the Concat function, as you can see below.

CASE 
WHEN COUNT(`Project_Manager`) = 1 
THEN CONCAT('Project Name: ',`Project_Name`,'
', 'Delivery Lead: ', `Project_Manager`,'
', 'Accountable Executive: ', `Project_Sponsor`) 
ELSE 'Please Filter on Project' 
END

CASE 
WHEN COUNT(`Project_Name`) = 1 
THEN CONCAT('Project Name: ',`Project_Name`,'
', 'Delivery Lead: ', `Project_Manager`,'
', 'Accountable Executive: ', `Project_Sponsor`) 
ELSE 'Please Filter on Project' 
END

Both outputs do not behave as expected and populate with the first project of the nine instead of the 'Please Filter on Project' as intended, even though there are 9 Project_names/managers etc.

Any help with this would be greatly appreciated.

Best,

Stuck

Find more posts tagged with

Bug

Beast Mode

Accepted answers

All comments

DashboardDude

Hi @Stuck ,

Any way you can show the actual data or a mock up? It'd be good to see a screenshot of what these fields look like and your formula to the right in a table to gain more context.

MichelleH

@Stuck Does your underlying data contain multiple rows for each Project Manager/Project Name? If all your projects are returning 'Please Filter on Project', then there is likely an issue with your COUNT() = 1 criteria.

Samuel.

Hi MichelleH, prior to filters, yes, after filters, no. The idea of the dashboard is that using a single select radio selector on the page users are able to reduce the number of projects to 1. With no filtering taking place it will return the "Please filter on Project". This criteria has worked on all other cards on my dashboard with no issue, but as soon as I add in that extra line of code it breaks down and stops working for just this card.

Samuel.

As for the data set - its mostly just as simple as project name, project manager and project sponsor. My solution is currently splitting out the "project Name" and all the other components into two cards, which works but doesn't look as tidy.

MichelleH

@Stuck Case statements tend to get messy when mixing aggregates with row-level data, which is likely the cause of why that is not working. Have you tried wrapping a MAX() around your CONCAT() to artificially aggregate your one row? If that doesn't work then it sounds like you've found a decent work-around by splitting the data into multiple cards.