Another ValueList Use Case

[Jennell_McIntire]

Several years ago, I blogged about how creating a synthetic dimension using ValueList allowed us to color dimensions in a chart. ValueList is commonly used where there is not a dimension in the data model to use, thus creating a synthetic one with ValueList. You can read more about ValueList in myprevious blog post. In this blog post, I am going to share how I used ValueList to handle omitted dimension values in a chart.

I recently ran into a scenario when creating visualizations based on survey data. In the survey, the participant was asked for their age as well as their age group. The ages were grouped into the following buckets:

• Under 18
• 18-24
• 25-34
• 35-44
• 45-54
• 55-69
• 70+

Once I loaded the data, I realized that there were not participants for all the age groups, so my chart looked like the bar chart below. There was a bar and value for only the age groups that the participants fit in.

While I could leave the chart like this, I wanted to show all the age group buckets in the chart so that it was evident that there were no participants (0%) in the other age group buckets. In this example, the four age groups were consecutive, so it did not look odd to leave the chart as is but imagine if there were no participants in the 45-54 age bucket. The chart may look odd with the gap between 44 and 55.

I explored various ways to handle this. One way was to add rows to the respective table for the missing age group. This worked fine but I was not a fan of adding rows to the survey table that were not related to a specific participant. The option that I settled on was using ValueList to add the omitted age groups. While this option works well, it can lead to lengthy expressions for the measures. In this example, there were only seven age group buckets so it was manageable but if you had many dimensions values then it may not be the best option.

To update the bar chart using ValueList, I changed the dimension from

To

Then I changed the measure from

To

Using ValueList in the dimension created a synthetic dimension with each age group option that was included in the survey. Now I will see all the age buckets in the chart even if there were no participants that fell in the age group bucket. Since I am using ValueList for the dimension, I need to update the measure to use it as well. This is where a single line measure can become a lengthier measure because I need to create a measure for every value in the synthetic dimension, thus the nested if statement above. The result looks like this:

There are no gaps in the age buckets, and we can see all the age bucket options that were presented in the survey. I prefer this chart over the first bar chart I shared because I have a better understanding of the survey responses presented to the participants as well as the response they provided. I would be interested in hearing how others have handled similar scenarios.

Thanks,

Jennell