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

Stuck
Stuck
in Charting

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

OR 

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

Tagged:

Answers

  • DashboardDude
    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
    MichelleH Coach

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

  • Stuck
    Stuck

    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.

  • Stuck
    Stuck

    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
    MichelleH Coach

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

Categories