Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ksab23
Helper I
Helper I

Custom 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.

UserCurriculum 1Curriculum 2Curriculum 3
Student 1CompleteCompleteIncomplete
Student 2IncompleteIncompleteIncomplete
Student 3IncompleteCompleteIncomplete


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.

UserCurriculaStatus
Student 1Curriculum 1Acquired
Student 1Curriculum 1Acquired
Student 3Curriculum 2Acquired


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!


1 ACCEPTED SOLUTION
v-nuoc-msft
Community Support
Community Support

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”

vnuocmsft_0-1704262385255.png

 

“Alternative dataset”

vnuocmsft_1-1704262413199.png

 

Create s column, count the completion of each course

vnuocmsft_2-1704262431084.png

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

vnuocmsft_3-1704262527859.png

 

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.

 

 

 

 

View solution in original post

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

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”

vnuocmsft_0-1704262385255.png

 

“Alternative dataset”

vnuocmsft_1-1704262413199.png

 

Create s column, count the completion of each course

vnuocmsft_2-1704262431084.png

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

vnuocmsft_3-1704262527859.png

 

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.

 

 

 

 

Ritaf1983
Super User
Super User

Hi @ksab23 

Please show the desired result

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.