How to creating a parameter driven list and search across multiple attributes

Imagine we have a long list of Values in a table. Now we want to filter out all lines that contain certain values but they might be spread across different columns. Lets see how we do this.

Filter Social Media Channels across different Columns

In the following example I have multiple marketing campaigns and for each campaign we use a different media (channel) mix to run the campaign.

The requirement was to select multiple social media channels and identify which campaigns were using which media. For example, which campaigns use Facebook or Instagram, in this case the answers would be all campaigns.

The data model did not contain a single table of all media channels and as you will see below the media channels were contained in 3 different fields.

How to do it?

Step One:

The first step in the solution was to create a global parameter of type text and a free from parameter type. The idea was to let the user type in a comma separated list of media channels.

Step Two:

The next step was to create 3 lists; Primary Channel, Channel 1 and Channel 2

For each list I used the following PQL, changing the reference to the appropriate attribute.

• Intersect  Returns a list of every element from an initial list that is also found in the second list.
• StrToSet  Convert string value to a set (List)
• Exists  Identify which campaigns contain the media channels entered in the global parameter

Here is the script:

{
                Exists(
                                {AllMembers([Channels].[Name])},
                                                Intersect(
                                               {StrToSet([Channels].[Channel1],[global].#[d86ff5f0-7631-4a3e-9cea-2dda01a312c1],",")}
                                                                                )
                                                )

Step Three:

Next, I created a List which combined (union) the values contained in the 3 lists; Primary Channel, Channel 1 and Channel 2

Step Four:

The resulting Discover allowed the user to enter different media channels in the Channel Search (Global Parameter) field using a comma separator.