Conditional DQL statements based on value of input variable

[HeadScratcher]

I have some DQL that presents a table of data of web service calls.  The data can be examined in different ways e.g. by consumer_id or application_name etc.   To achieve this I either have to edit query each time or create multiple tables on a dashboard. 

How can I avoid this?  Can I create an input variable called Choice (holding different DQL text) and then use that to visualise the data from different views? 

// Summarise the output... 
| summarize hit_count = count(),by:{ $Choice, response_out_http_code }

When I used the above  the $Choice resolves to the string "consumer_id" but the DQL is treating it as a string literal and not actually using it in the query. 

What  am I doing wrong? 

[krzysztof_hoja · Accepted solution]

variables are always strings and are treated as literals in DQL query. They cannot be used as a field names, commands or function names. The workaround is possible using this trick:

data record(a=1, b="a"),
record(a=1, b="a"),
record(a=2, b="b"),
record(a=2, b="b"),
record(a=2, b="b"),
record(a=3, b="c"),
record(a=3, b="d"),
record(a=3, b="e"),
record(a=3, b="e"),
record(a=4, b="e")
| fieldsAdd split=coalesce(
  if($splitField=="a", a),
  if($splitField=="b", b)
)
| summarize cnt=count(), by: { split }

depending on variable value, proper field value will be kept under added filed of the same name independent from the choice. Now it can be directly used in a query (in by: {}).

Kris

[HeadScratcher]

I thought there must be a way.     That is a pretty neat solution and actually helps tidy up my DQL a little bit too.