I have a big complex issue with quicksight, which I have two different data sets of “The hierarchy of the location” and “orders” ’ which I can’t join. to simplify it and get to the point I’ll ask briefly it:
I want the “Orders” dataset will reflect the “Hierarchy of the location” so when user will choose any location to filter the orders, the dashboard will show all the orders that refer to this location. For example- if the user will fiter “Israel” it will show the order that thier location value is “Tel aviv” or “Jerusalem” and alse “duvdevan street” and “Haim street” which belong to israel too. So in my mind it should be so that the value of this chosen filter field will send to a parameter, and this parameter influence on a calculated field in the “The hierarchy of the location” so this calculated field will output all the values which are relevants to the chosen of location and those value will be sent back to the “Orders” data set and will filter it according to those value . the problem is that as I understand and researched there is no option to send by calculates field values to parameter. so I ask to understand if there is no way to send by calculated field values to other parameters? I mean, it should be behind the scene, so the user shouldn’t be involved in this.
If there are such other insight I would like to hear about them,
Thx in advance,
Noy
I am not sure if this is possible in your case because of your constraints on the join, but one possibility could be to create the calculated field in the dataset that gives you the location and then use that for the parameter of the filter.
Hi @andres007 ,
Thanks for your response!
The thing is that I need to calculate the appropriate locations reffering to the selected one. It’s means that the calculated field should get a location value and then need to check which other locations should output in this selection - so there should be outputs in many values and not a single value.
So in an Ideal case I should use a calculated field that output multiple values into few parameters, but I think It’s impossible.
What i am trying to propose is that you have the logic of your hierarches to return a single location for all the relevant ones as a calculated field logic since you cannot perform the join with the hierarchies data set. That could be done using the functions ifelse() in conjunction with other useful list functions like in() to make the calculation smaller and more readable, but it will depend on how big is the dataset of hirarchies and how much it changes over a period of time.
There would be other options to do this with using the Show relevant values only for parameters and using the paramter as a filter to the second visual, something like this:
But due to the way QuickSight works when selecting all the values then the filter is removed, with no option for sending all the relevant values in a list, this solution will not really work for your use case.
Another option, if you can access the table with the hierarchies and the orders from the same dataset, you could try performing a sub query using dataset parameters.
in that case your Custom SQL could be built in this way.
select * from address
where city_id in (select city_id from country where country_id = <<$countryid>>)
But if this is possible, I would not see why not just join both tables and use the relevant field as a normal filter.
I will mark this as a feature request, the idea to have a toggle where we can define if the “Select All” will send the list of values even if it is the complete list can be useful in many situations.
Hi @andres007 ,
Thanks you so much about your detailed explanation.
I like the direction this is going in. Actually I didn’t successed to execute it, because in the sub query I should select columns depending on in which columns the city_id is, something like that:
if city_id in col0 then you should give all the city_id that in( column 0,1,2,3,4)
if city_id in col1 then you should give all the city_id that in( column 1,2,3,4)
if city_id in col2 then you should give all the city_id that in( column 2,3,4)
if city_id in col3 then you should give all the city_id that in( column 3,4)
if city_id in col4 then you should give all the city_id that in( column 4)
but then it’s too complex to the sql
Thanks again,
Noy
