Dynamic Breakdown Dimension with Mixed Data Type (Text and Number)

Question

Is it possible to make a dynamic Breakdown Dimension with mixed Data Type?

Basically I want the user with view only permission to dynamically change Breakdown Dimension for Q1-Qn chart's with C1-Cn Breakdown Dimension

I tried with the following step:

Create Parameter breakdown by parameter
Create a Custom Field with this formula:

CASE breakdown by parameter
  WHEN "C1" THEN C1 
  WHEN "C2" THEN C2
  WHEN "C3" THEN C3
  WHEN "C4" THEN C4
  WHEN "C5" THEN C5
END

Add Control for the parameter
Use the custom field in Chart's Breakdown Dimension

But it says Invalid formula - THEN/ELSE arguments have incompatible types: TEXT/ NUMBER. All THEN/ELSE arguments must return the same type.. Is there a workaround for that ? it's working fine if the breakdown dimension is text only or number only.

I also use a control to filter the data, so I want to use control to filter C1-Cn columns and to set breakdown dimension to C1-Cn columns

user_12892553 · Answer

As noted in the error message (emphasis mine):

Invalid formula - THEN/ELSE arguments have incompatible types: TEXT/ NUMBER. All THEN/ELSE arguments must return the same type.

Thus (since all but one of the arguments return a TEXT type), one approach is to CAST the only NUMBER field C2, AS TEXT, so that all values in the calculated field are text values:

CASE breakdown by parameter
  WHEN "C1" THEN C1
  WHEN "C2" THEN CAST(C2 AS TEXT)
  WHEN "C3" THEN C3
  WHEN "C4" THEN C4
  WHEN "C5" THEN C5
END

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

Dynamic Breakdown Dimension with Mixed Data Type (Text and Number)

1 Answers