Dropdown filter kibana canvas

[wadhah]

Helo,
I have a question please : I am at the moment working on a canvas dashboard and I am using a dropdown column that I want it to be applied properly on the whole canvas dashboard knowing that I am querying data from multiple indexes. The problem that I am getting is that this filter is only applied on the charts sharing the same index as the filter, and the other charts(using other indexes) get null values. So, How can I solve this problem?

And is there a way, when it's needed, to apply the dropdown column on a specific chart without influencing other charts?

[LizaD (Liza Dayoub)]

@clintandrewhall can you help ?

[clintandrewhall]

Good morning!! I have good news and less good news, depending on your perspective.

We introduced the concept of "group filtering" recently, which I believe will address your use case! It was backported to Kibana v7.2.0, which should be released very soon...

Still, I'm going to pass this on to our team to be sure this feature will solve the null values problem, in case there is a mitigation we can provide now. In the meantime, can you confirm if the above PR is what you're talking about? Thanks!

[clintandrewhall]

I chatted with our team, and one thing you can do for other elements is to remove the filters | portion of the expression at the very beginning. This would allow you to apply the global filters to an element whose index matches, and other elements wouldn't take those filters into account. I realize that would make those other elements non-filterable... but until we ship 7.2, this would be the only way to mitigate.

I hope that helps!!

[wadhah]

Thank you so much @clintandrewhall for your quick update. I have just upgraded my ELK stack and the problem is not fixed. Let's say I have two indexes(index1, index2) sharing one of the data, let's call it category.
This is what I am using for the filter:
SELECT category
FROM index1
WHERE category!='null'
GROUP BY category

So the filter will be applied on all the charts related to index1 whereas the charts related to index2 will get null values(if they are metrics) or disappear in the case of pie charts......
So please can you help me to solve this problem

[clintandrewhall]

Take a look at the release notes for 7.2:

Kibana 7.2.0 released

Ensure your filter is targeted at the proper index and your elements use the correct group. Does this help?

[wadhah]

Hello @clintandrewhall thank you for your response. However this new feature of the dropdown column is not helping in my case.

[clintandrewhall]

I guess I'm not understanding what the issue here is, specifically. Filter groups should allow you to apply filters to individual elements.

Can you restate, in detail, what you're trying to do? Screenshots would likely be helpful here.

[wadhah]

Thank you again @clintandrewhall for your help.

As I told you before : I have multiple charts in my dashboard and I am querying data from different indexes. Let's say I have two pie charts : the 1st is displaying SUM(costs) and different resources (for this I am relying on "index1"). And the 2nd chart is displaying COUNT(status) and different status (for this I am depending on "index2").
For the filter I am using these SQL commands :
SELECT category
FROM index1
WHERE category!='null'
GROUP BY category
->I've chosen "category" for "values column" also for "filter column"
For the record, "category" is shared by both indexes by that I mean that "category" is a common field for both indexes.

So basically the filter will be applied only on the 1st pie chart however I need it to be applied on both charts. I found a solution which is to use two filters and assign each one for each chart based on "filter group name". However, I want to use just one dropdown column.
I hope that my situation is clear now

[clintandrewhall]

Are you doing this in Canvas? Or just in Kibana Dashboard? Because I'm not seeing anything here relating to Canvas...?

[wadhah]

In canvas

[Catherine_Liu]

@wadhah You should be able to filter both elements using the same dropdown filter that filters on your category field.

Here's an example that I got working in my Canvas workpad using sample data and a mock data set which both have a customer_gender field.

Demo:

Dropdown filter element:

esdocs index="kibana_sample_data_ecommerce" fields="customer_gender"
| dropdownControl valueColumn="customer_gender" filterColumn="customer_gender" filterGroup="gender"
| render

Data table element #1 using the kibana_sample_data_ecommerce index:

filters group="gender" ungrouped=true
| essql query="SELECT user, customer_gender FROM \"kibana_sample_data_ecommerce\""
| table
| render

Data table element #2 using my mock data from the mock_people_data index:

filters group="gender" ungrouped=true
| essql query="SELECT * FROM \"mock_people_data\""
| table
| render

Note: For all other elements you don't want to be effected by this filter, pass ungrouped=true to the filters function at the beginning of all of those elements, which indicates that the element will only receive global filters with no group name assigned. This way it won't be filtered by any filters that belong to a group, i.e. have a group name assigned to them.

[wadhah]

Thank you so much @Catherine_Liu for your response. I tried it but it didn't work.
Here's the first metric:

filters group="group1" ungrouped="true"
| essql
query="SELECT round(SUM(Costs),2) AS sum
FROM "consumption*"
WHERE MONTH(usageEnd)=MONTH(CURRENT_DATE())"
| math "sum"
| metric "$"
| render

Here's the 2nd mteric:

filters group="group1" ungrouped=true
| essql query="SELECT COUNT(category)
FROM "advisor*"
WHERE category='security'"
| math "COUNT_category_"
| metric ""
| render

Here's the filter:

esdocs index="consumption*" fields="subscription.keyword"
| dropdownControl valueColumn="subscription.keyword" filterColumn="subscription.keyword" filterGroup="group1"
| render

And as usual the first metric is being filtered correctly however the 2nd metric takes "0" as a value when the filter is not on "any"

[tims (Tim Schnell)]

Hey @wadhah, just to clarify the advisor* and consumption* indices both have the keyword analyzed field?

[wadhah]

yes @tims

[Catherine_Liu]

@wadhah Can you provide a sample of the data in both indices? Are the values in both subscription.keyword fields the same including text case? The filter is case sensitive, so that could be the issue.