filter table base on selected bookmark

Ravin1 — Sun, 06 Feb 2022 09:51:10 GMT

Hi team, I have two tables. Table 1(Main table) and Table 2(Item table). I want to filter Table 2 base on the value selected in Table 1 by clicking button or automatically other wise I want Table 2 remain empty.

Table 1:

Table 2:

Example scenario:- Let say, I selected value " ID=8 " in Table 1 then Table 2 should filter "ID=8". So, I able to see the items for the selected ID only. Once clear the selected value then Table 2 need to be empty.

Re: filter table base on selected bookmark

Andrei_Cusnir — Wed, 09 Feb 2022 08:45:18 GMT

Hello,

If my understanding is correct, you would like to filter the data in Table 2 based on the selected ID values of Table 1, but in the event that non of the IDs are selected, the table should display nothing. This can be done with different ways:

A. This case describes the scenario where IDs are linked.

1. If you load the following dataset:

You will notice that the ID field in both tables is the same. So they are linked together and thus the Data model viewer shows the following output:

As you can see both ID fields are linked with each other, which means that if you select the ID 8 on the Table 1, it will also filter the data on the second table as follows:

2. Now if you want to avoid displaying any data if non of the IDs is selected, then you can go to Edit sheet > Select Table 2 > Add-ons > Data handling > Calculation condition and use the expression =GetSelectedCount(ID) > 0. The outcome is:

As you can see it first checks if there are any IDs selected and if this statement is true then it allow the display of the data, otherwise it will show a custom message for unmet condition.

B. In case the ID fields in both tables have different names and thus are not linked with each other, you have to follow a different approach. For example here is the dataset:

As you can see the ID field in first table is now named ID1 and the ID field in second table is now named ID2. Therefore, those 2 IDs are not linked with each other in the Data model viewer:

1. For the second table instead of using ID2 as the first dimension, I have used the following expression:

=If(WildMatch(GetFieldSelections(ID1), '*' & ID2 & '*'), ID2, Null())

This expression will first get all the selected fields from ID1 and will check to see which IDs from ID2 field are matching. If the ID match it will return the ID, but otherwise it will return Null().

2. Now I have un-checked the option "Include null values" for that dimension

3. Under Add-ons > Data handling > Calculation condition I have used the expression =GetSelectedCount(ID1) > 0 which checks if any IDs are selected from the first table.

4. The outcome if non of the IDs are selected is:

5. The outcome if one ID is selected is:

6. The outcome if more than one IDs are selected:

As you can see if non of the IDs are selected the second table displays a custom message for unmet condition, otherwise it filters the second table based on selected values from the first one.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.

topic filter table base on selected bookmark in Visualization and Usability

filter table base on selected bookmark

Re: filter table base on selected bookmark