Set analysis/wildmatch/SQL like statement

[markobanjanin]

Hello,

i have 2 tables

County:

Country Code, Country Name, Region

ABC, A, R1

QWE, B, R1

POI, C, R1

ZXC, D, R2

BBB, E, R5

...

MainTable:

ID, Qty

2020_ABC_POI, 1

2020_ABC_ZXC, 2

2020_ZXC_BBB, 7

From the main table ID field contains year + 2 country code references separated by _

On the dashboard I am presenting users with a Region and Country Name filters. Based on country selection I am storing country codes in a variable. In a KPI object i want to sum Qty field based on my selections. For example:

Region selection = R1 --> sum should be 3

Region selection = R2 --> sum is 9

Country selection = A -> sum is 3

Country selection =  A and D --> sum is 10

My set analysis is the following: sum({<ID= {"*$(=$(variable))*"}>} Qty)

The variable concatenates Code field with , separator.

Issue with above is that it only works when i select a single country. I need the set analysis to take into account all Country Codes as per my selection

Thanks!

[Kushal_Chawda]

Why don't you split the ID column in main table in Year & Country code and link on Country code with Country table?

[markobanjanin]

Those 2 tables come from my data warehouse. Country = dimension, which contains regions. Main table = fact. The ID column is a business key. Tables are joined by country_sk

i don’t want to deviate from that design and further denormalize the fact

[Vilius]

I'm not sure if it works but could you try to concat the variable using separator

*", "*

or

*), $(*  

so the final variable result of the filter statement represents correct syntax for multiple matches?

[jonas_rezende]

Hi, @markobanjanin .

Please, share content of your variable.