Hello, I have a scenario where there is a filter on which I need to make a calculation based on the selection in the filter. I cannot go with parameter as this filter is again a dynamic calculation based on another parameter. For example I have a parameter P1 and filter F1. The values in F1 are dynamically calculated based on selection in P1. Hence I cannot link this field to another parameter to capture the selection. Kindly let me know if there is any workaround to achieve the same.
hi @krishna.c
thank you for posting your question. Would you be able to describe what you want to achieve in dashboard? would there be several filter controls ?
kind regards,
Wakana
Hi @Wakana , Thank you for your response. I have explained the scenario below. Please have a look.
I have a scenario where there is a filter on which I need to make a calculation based on the selection in the filter and the values in this filter are populated based on selection in a parameter. For example I have a parameter “Search By” which holds the value “Region” & “Country”. Based on the user selection a calculated column values need to be populated which is used as filter in report, like if the user selects “Region” then in the filter drop-down, Region values should be populated and likewise if country is selected, Country values should be populated during the report run-time. And then there is a column called “State” which contains one of it’s value as “@NA”. Now the ask is the records with STATE as “@NA” need to be shown only when the user selects “SELECT ALL” option in filter and “Region” is selected in Parameter, otherwise those records need to be filtered-out from the visual. In other words, if the user selects “Region” in parameter and any specific REGION in filter then the records that contains “State” as “@NA” should be eliminated from the visual. Since it is not possible to compare “Select All” in calculation and filter data, Kindly suggest how this can be achieved? Attached sample data image for reference. In the attached image, if the user selects EAST, then in the visual, the records with STATE as @NA should be filtered out and only record with KKK should be displayed. Similarly for WEST only record with III should be shown and so on for other regions as well. Thanks.
hi @krishna.c
thank you for explaining the detail of the use case.
the parameter value when ‘select all’ being selected should be ‘All’ as you can see here in demo central.
kind regards,
Wakana
Hi @Wakana , Sorry, My use case is not ‘Select All’ in parameter. I might not have explained it correctly. I will try to put in different way.
Step 1: User will select from a Parameter to see the visual at Region/Country level.
Step 2: Based on user selection in parameter, I have a calculated field which populates Region values when REGION is selected and COUNTRY values when country is selected in the parameter.
So, now I have 1 parameter & 1 calculated field. And this calculated field is used as filter in the visual through which user can filter the required REGION(s)/COUNTRY(S). And then there is another column called STATE which contains one if its value as “@NA”. So if the user selects either REGION or COUNTRY in the parameter, the data contains some records for STATE as “@NA”. But this “@NA” records should be shown in the visual only when the FILTER(Calculated column based on parameter) is in “SELECT ALL” and in case if the user selects any particular REGION or COUNTRY then the records that are having STATE as “@NA” should be filtered out from the visual.
For example, In the image attached, if the user selects REGION in parameter and EAST in calculated column filter then the output should contain only the records with STATE having “KKK” and the remaining records with STATE as @NA should be eliminated. So basically I do not have a explicit filter on STATE column but the @NA records should get filtered based on the selection in other filter which is a calculated column based on parameter.
Thanks.
Hello @krishna.c, I think your best option would be to replace the {state} field in your table with a calculated field that utilizes an ifelse statement. You will check if your region or country field equal the equivalent parameter, if so we will ignore “@NA”, otherwise we will return the value for state.
It would look something like this:
ifelse(${REGION} = {region} OR ${COUNTRY} = {country} AND {state} <> '@NA',
{state}, ${REGION} <> {region} AND ${COUNTRY} <> {country}, {state},
NULL)
This should handle only returning state values not equal to “@NA” when the Region or State is selected and should also handle returning all state values when select all is used.
Hello @DylanM , Thanks for the solution. I have achieved it through parameters instead of using it as a filter.