CODEWITHSUNDEEP
looker-studio
Andriyansyah Nurrachman
Andriyansyah Nurrachman

Reputation: 131

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

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

This is the data source sample

This is the Report I Tried

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:

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

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

Upvotes: 1

Views: 1342

Answers (1)

Nimantha
Nimantha

Reputation: 6471

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:

5

Upvotes: 1

Related Questions