Ifelse with mutliple conditions connecting to parameter/field and all visuals

Question & Answer
, ,
1

ifelse
ifelse1098×394 57.4 KB

I am trying to create a control that will change when I click on the name drop down list like Ashburn. Its not calling the city names. The city names are also not a field.
I created a control parameter called siteselection and input the city names below.

I have this ifelse statement
ifelse
(
${SiteSelection} = ‘Ashburn’, site = ‘IAD006,IAD013,IAD050,IAD054,IAD060,IAD071,IAD116,IAD117,IAD161,IAD162,IAD606’,
${SiteSelection} = ‘BullRun’, site = ‘IAD007,IAD011,IAD024,IAD075,IAD077,IAD085,IAD095,IAD096,IAD108,IAD109,IAD113,IAD130,IAD131,IAD132,IAD133,IAD222,IAD370’,
${SiteSelection} = ‘Chantilly’, site = ‘IAD061,IAD063,IAD065,IAD086,IAD134,IAD150,IAD51,IAD152,IAD155,IAD600’,
${SiteSelection} = ‘Dulles’, site = ‘IAD010,IAD032,IAD078,IAD079,IAD080,IAD098,IAD099,IAD114,IAD115,IAD118,IAD119,IAD129,IAD163,IAD164,IAD601’,
${SiteSelection} = ‘Herndon’, site = ‘IAD012,IAD015,IAD016,IAD051,IAD056,IAD076,IAD088,IAD089,IAD144,IAD145,IAD146,IAD147,IAD175,IAD192’,
${SiteSelection} = ‘Manassas’, site = ‘IAD014,IAD035,IAD052,IAD059,IAD054,IAD055,IAD073,IAD074,IAD084,IAD100,IAD101,IAD102,IAD103,IAD104,IAD105,IAD223,IAD602,IAD604’,
${SiteSelection} = ‘Sterling’, site = ‘IAD057,IAD058,IAD090,IAD091,IAD092,IAD140,IAD141,IAD142,IAD226,IAD227,IAD603,IAD608’,
${SiteSelection} = ‘StoneRidge’, site = ‘IAD001,IAD009,IAD022,IAD062,IAD068,IAD081,IAD083,IAD093,IAD120,IAD121,IAD122,IAD609,IAD614’,
NULL

2

Hi @Alexis_Parson , I wanted to clarify a few things to help you figure out your solution.

Your calculated field is using a parameter control with all of the city names that you are using to link to the site names but those city fields are not actually in your dataset?

Also, what are you wanting this calculated field to display/do? Are you wanting the site to display on a certain visual (or all visuals) when mapping to a city?

Without all of the information, I believe the correct course of direction here would be adding the cities to your dataset so they are mapped on each row. You could also create another version of this exact calculated field but switch it to if(site = x, ‘Ashburn’, site = y, ‘BullRun’, etc.), then add a filter for calculated city.

3

Hi, @DylanM

Yes, the city fields are not in the dataset.

Each city has 8-10 IAD sites associated with it. I do a field with all the sites in the world

I created a control so that when I click on the the city all the visuals show information for that city/sites only.
names

Should I create an excel file with the city names and then upload to my already created dataset?

4

@Alexis_Parson thanks for the quick response! I do think adding them into the dataset would be helpful. If you wanted to add it, if your dataset is a CSV you can add the column in through excel but if it is built from custom SQL or a data source view it will have to be done there.

What exactly is the problem you are experiencing in regards to the city selection?

5

clus
clus642×652 23.2 KB

Hi, @DylanM
I added in a csv file with the names as pictured above.
Now that I have a field with the names. How would I set up the parameter and calculated field or filter.

would I create a parameter called location and then something like this for the calculated field?
Apply filter after that?

ifelse (
        clusters='Ashburn','IAD006,IAD013,IAD050,IAD054,IAD060,IAD071,IAD116,IAD117,IAD161,IAD162,IAD606',
        clusters  = 'Bullrun','‘IAD007,IAD011,IAD024,IAD075,IAD077,IAD085,IAD095,IAD096,IAD108,IAD109,IAD113,IAD130,IAD131,IAD132,IAD133,IAD222,IAD370',
        clusters  = 'Chantilly','IAD061,IAD063,IAD065,IAD086,IAD134,IAD150,IAD51,IAD152,IAD155,IAD600',
        clusters  = 'Dulles','‘IAD010,IAD032,IAD078,IAD079,IAD080,IAD098,IAD099,IAD114,IAD115,IAD118,IAD119,IAD129,IAD163,IAD164,IAD601',
        clusters = 'herndon','IAD012,IAD015,IAD016,IAD051,IAD056,IAD076,IAD088,IAD089,IAD144,IAD145,IAD146,IAD147,IAD175,IAD192',
        clusters  = 'manassas','IAD014,IAD035,IAD052,IAD059,IAD054,IAD055,IAD073,IAD074,IAD084,IAD100,IAD101,IAD102,IAD103,IAD104,IAD105,IAD223,IAD602,IAD604',
        clusters  = 'sterling','IAD057,IAD058,IAD090,IAD091,IAD092,IAD140,IAD141,IAD142,IAD226,IAD227,IAD603,IAD608',
        clusters  = 'stoneridge','‘IAD001,IAD009,IAD022,IAD062,IAD068,IAD081,IAD083,IAD093,IAD120,IAD121,IAD122,IAD609,IAD614',
        Null
)
6

@Alexis_Parson if you added the city fields to the dataset rows, you should only have to filter the visual based on the city select value because that will be mapped to the IAD sites within the row so only those would appear.

If that doesn’t work correctly through your implementation of the city field within the dataset, you could instead add them in a calculated field where you use the IAD sites value to determine which city they are linked to and filter based on the calculated field for the parameter selection.

Calc field option:
ifelse(
site = ‘IAD006,IAD013,IAD050,IAD054,IAD060,IAD071,IAD116,IAD117,IAD161,IAD162,IAD606’, ‘Auburn’,
site = ‘IAD007,IAD011,IAD024,IAD075,IAD077,IAD085,IAD095,IAD096,IAD108,IAD109,IAD113,IAD130,IAD131,IAD132,IAD133,IAD222,IAD370’, ‘BullRun’,
etc.)

7

Did my response help you find a solution to your QuickSight question?