Get data from other table in pie chart

[harshadrokade]

HI Experts,

Need help on below query.

I have 2 tables as below

Table1 (All students)

Student id

Student name

Manager name

Table2 (Only those Students who have not completed the training)

Student id

Training name

I want to create a pie chart which will show break up of students from Table 1 that have completed the training vs not completed a specific training. There will be slicer based on the Trainingname from Table 2

How to bring all this data together so that I 

[v-yangliu-msft · Accepted solution]

Hi  @harshadrokade ,

I created some data:

Table1：

Table2：

Here are the steps you can follow：

1. Create measure.

not completed the training =
var _select=SELECTEDVALUE('Table2'[Training name])
return
COUNTX(FILTER(ALL('Table2'),'Table2'[Training name]=_select),[Student id])

completed the training =
var _select=SELECTEDVALUE('Table2'[Training name])
return
COUNTROWS('Table1 (All students)') - COUNTX(FILTER(ALL(Table2),'Table2'[Training name]=_select),[Student id])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SpartaBI]

@harshadrokade if both tables have one row per student (as in the student id column is a unique identifier) then regardless your issues, best practice to merge them to one table (and this is of course will make your solve your requirement easily).
Is this the situation?
Let me know if you need help.

      

Showcase Report – Contoso By SpartaBI

[harshadrokade]

Thanks @SpartaBI  for your reply. 

The Table 1 is the complete table where all students are listed & only one entry provided for a student

But the Table 2 is having only those students who have not completed the trainings . Also the Table 2 can have one student multiple times as there I also have training name column. If a student didnt complete multiple trainings, he will be listed multiple times in Table 2 but the Table 1 will have his name only once. Thats why I am unable to merge the data, can I still merge the data in new table & use it in pie chart?

[SpartaBI]

@harshadrokade in this case, then keeping them split is the right choice like you did. Do you want to do a quick zoom and show me your scenario?

[harshadrokade]

Thanks for the reply again. Not allowed to upload pbix file or zoom call with student info 🙂

[SpartaBI]

@harshadrokade maybe you could create a fake sample PBIX that we could look together in zoom so then you could see how to do that on your file