Forum Discussion
Helper ICustom legend from existing dataset
Hi all and happy New Year!
I have a very peculiar dataset from which I need to build a stacked bar visual or any other bar visual. My 'Master dataset' consists of a user list in rows and curriculum completion status in columns, see below.
| User | Curriculum 1 | Curriculum 2 | Curriculum 3 |
| Student 1 | Complete | Complete | Incomplete |
| Student 2 | Incomplete | Incomplete | Incomplete |
| Student 3 | Incomplete | Complete | Incomplete |
For the stacked bar chart I would like to see the following:
Y-axis - Curricula name
X-axis - user list
Legend - Complete/Incomplete
Now, I have an 'Alternative dataset' that can provide me with partial information only, it contains a list of users who completed a specific curriculum, but not incomplete or any other status (in this specific example, Student 2 is not even on the list as they didn't complete any curricula). I unfortunately need both. The alternative dataset looks like this.
| User | Curricula | Status |
| Student 1 | Curriculum 1 | Acquired |
| Student 1 | Curriculum 1 | Acquired |
| Student 3 | Curriculum 2 | Acquired |
I was able to build charts with other 'Legend' fields, such as student level, as it is specified in the dataset. But the 'Complete/Incomplete' status is brought in with the calculated columns to Master Dataset:
(IF=('Master Dataset'[Curriculum 1]>=1, "Complete", "Incomplete"))
'1' coming from:
CALCULATE (COUNTA('Alternative dataset'[Status]), 'Alternative dataset' [Curricula] IN {Curriculum 1}).
Can anyone help me with this, please? Really appreciated!
Hi ksab23
Based on the questions you asked, my guess is that you might want to show the course and completion, here's how I offer it:
Here is the data you provided
“Master Dataset”
“Alternative dataset”
Create s column, count the completion of each course
Complete/Incomplete = if(CALCULATE(COUNT('Alternative dataset'[Status]), FILTER(ALL('Alternative dataset'), 'Alternative dataset'[Curricula] = EARLIER('Alternative dataset'[Curricula]) && 'Alternative dataset'[User] = EARLIER('Alternative dataset'[User] ))) >= 1, "complete", "incomplete")Create a visual, here is the result
If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi ksab23
Based on the questions you asked, my guess is that you might want to show the course and completion, here's how I offer it:
Here is the data you provided
“Master Dataset”
“Alternative dataset”
Create s column, count the completion of each course
Complete/Incomplete = if(CALCULATE(COUNT('Alternative dataset'[Status]), FILTER(ALL('Alternative dataset'), 'Alternative dataset'[Curricula] = EARLIER('Alternative dataset'[Curricula]) && 'Alternative dataset'[User] = EARLIER('Alternative dataset'[User] ))) >= 1, "complete", "incomplete")Create a visual, here is the result
If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
