Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dimension which is a list of unique countries ("Country(unique)") with a measure of rows ("Nid" which acts as a primary key and ID for each row). Each row in my data contains a field that lists one or multiple countries ("Countries") separated by commas. I want to count the number of rows (Nid) for which each unique country appears.
Using the following formula, I am able to count the number of rows that contains (and only contains) Brazil.
Count({<Countries={'Brazil'}>}Nid)
Instead, I need to count all rows that contains the country and any other text/country for each country listed as a dimension. How do I go about doing this?
Not 100% sure, but may be this:
Count({<Countries={'*Brazil*'}>}Nid)
The wildcard works to count anything including the word Brazil, so we're partly there. The bigger challenge is doing the same thing for each dimension (country).
I think it would be better to make changes in the backend of the script to handle this in a better way.
Create a new table like this:
LOAD Countries,
SubField(Countries, ',') as Country(unique)
Resident SomeTable;
Where Country(unique) name matches with the field name for the field where Country is uniquely listed and is tied to Nid