7 Replies Latest reply on Jan 15, 2020 8:20 AM by Ken Flerlage

# Create bar chart of highest value of a dimension seen per user

I believe this is a simple visualization but I can't figure it out.

Imagine a group of people doing online lessons.  We want to see a horizontal bar chart of where people are in the process.   For example, how many people have trained through lesson 1, how many through lesson 2, etc.

The attached packaged workbook v2019.3.1 contains example data and a sketch of the wished-for horizontal bar chart.

• ###### 1. Re: Create bar chart of highest value of a dimension seen per user

Seems like it should be easier than the solution I'm about to give, but I can't think of another way to do it. So, I'd start by creating a field to get the maximum for each student. We'll need an LOD for this:

Max Lesson

```// Maximum lesson for the student.
{FIXED [User Name]: MAX([Lesson])}
```

Then we'll use that on the rows shelf and a COUNTD of Username on the columns shelf: See attached.

• ###### 2. Re: Create bar chart of highest value of a dimension seen per user

Thank you!   This gets me most of the way there.  Do you have any thoughts on how to force it to show Lesson 4 =0 ?

• ###### 3. Re: Create bar chart of highest value of a dimension seen per user

I figured out a hacky way to accomplish this by introducing null users into the data.  I'm sure there is a more elegant way. • ###### 4. Re: Create bar chart of highest value of a dimension seen per user

Technically speaking, Lesson 4 doesn't even exist in the new field we've created so it doesn't show. Your solution is one method to force it to show, but the drawback is that you have to manually change the data. Another option would be to union the data to itself so we can create some extra data in a more automated fashion. This creates a new Table Name field we can use in our calculated fields. We can change Max Lesson to:

Max Lesson

```// Maximum lesson for the student.
IF [Table Name]="Sheet1" THEN
// First table. Get the actual max lesson.
{FIXED [User Name]: MAX([Lesson])}
ELSE
// Second table. Just get the lesson.
[Lesson]
END
```

Then create a new measure for your count:

User Count

```// Only count from the first table.
COUNTD(
IF [Table Name]="Sheet1" THEN
[User Name]
END
)
``` There are a couple of problems with this approach though. Primarily, it assumes that all the lessons have been covered at least once. That's true in this data set, but may not always be true.

• ###### 5. Re: Create bar chart of highest value of a dimension seen per user

Another option is to do something similar to what you did above, but instead of adding it directly to your data source, create a separate spreadsheet with just those fake records. Then, in your data model, union that data set to your original data set. Then the calcs are all the same as what you've used. By doing this, you can avoid having to insert those records into your original data set.