Dear reader,
The title might be a little confusing. I'll explain what I wish to do and I hope that clarifies my question.
I wish to create 2 identical charts next to eachother, apart from 1 detail.
I've got the data below, sadly this is spread out over different tables (a table for employee, a table for incident, and a table connecting them.)
| %Employee_key | Employee.Name |
|---|
| 1111 | AAAA |
| 2222 | BBBB |
| 3333 | CCCC |
| %Incident_key | Incident.Category | Incident.TSOC |
|---|
| 11 | A | 10 |
| 22 | A | 5 |
| 33 | A | 25 |
| 44 | B | 15 |
| %Employee_key | %Incident_key |
|---|
| 1111 | 22 |
| 1111 | 44 |
| 2222 | 11 |
| 3333 | 33 |
Now I wish to show 2 bar charts next to eachother, each showing the TSOC per Category. This is easily doable, BUT... Depending on my selection I want the first chart to show it for one employee, and the second for another.
So if I choose Employee 1111 and Employee 2222, the first chart shows this info for Employee 1111 and the second for Employee 2222.
But if I choose Employee 2222 and Employee 3333, the first chart shows this info for Employee 2222 and the second for Employee 3333.
Now ofcourse I could use a Fixed Number limitation and choose Top 1 and Bottom 1, but I'd like to find a way to achieve this for 3, 4, or even 5 charts next to eachother.
I tried to achieve this using the aggr() function, but if my understanding of it is correct this create a new table of whatever is calculated by the aggr() function and thus it loses all connection to other dimensions.
firstsortedvalue(aggr(SUM({$<Type={'Incident'}>}[Incident.TSOC]),[%Employee_key),[%Employee_key])
firstsortedvalue(aggr(SUM({$<Type={'Incident'}>}[Incident.TSOC]),[%Employee_key]),[%Employee_key],2)
These are the formula I tried to use (the Incident type is because the table also contains Problems and Changes).
Sadly, since the aggr() loses the connection to Incident.Category it is unable to connect itself to the Category dimension I've set up.
Ofcourse the result of this formula is only an Employee key (due to the firstsortedvalue), and not the values per category...
Something else I've tried is to add the firstsortedvalue to the measure like this:
SUM({$<Type={'Incident'},[Employee.Name]={Aggr(firstsortedvalue([Employee.Name],[%Employee_key]),[Employee.Name])}>}[Incident.TSOC])
Sadly this results in this error: "Error in set modifier ad hoc element list: ',' or ')' expected."
It took me a while to settle for firstsortedvalue, simply because this worked in a table. I was trying to find a way to choose the first selection in my filter, and then the second selection, and this was the only way I found that worked:
firstsortedvalue([Employee.Name],[%Employee_key])
firstsortedvalue([Employee.Name],[%Employee_key],2)
Does anyone have any better ideas how to solve this issue?
Yours Sincerely,
Casper Westelaken
