Count unique IDs for teacher observations and include zero if there are no IDs for a teacher.

[RossKnorr]

I'm finding it very difficult to summarize this problem in the subject line...

Hi Community,

In my query list I have two tables.

- One is a list of teachers with just one column: a list of teacher names.

- The second is a Sharepoint list that collects observations of these teachers with five yes/no questions per observation. I've unpivoted those yes/nos so that there are five rows for each observation. 

Not all teachers have received an observation, so there are names in the first table that don't exist in the second. Each observation in the second table has a unique ID number, and I want to create a chart on my Power BI report that will show the teacher name and the number of unique observations they had. However, I also want it to display the names of teachers who have had an observation yet, with a zero to show the number of observations.

I know how to do a distinct count to get the number of observations for teachers who HAVE had an observation (those that exist in the second table) and I think I need to join these two tables somehow to also show those with none, but I'm not sure how to do this and create the visualization I need.

Can anybody point me in the right direction?

[v-alq-msft · Accepted solution]

Hi, @RossKnorr 

Based on your description, I created data to reproduce your scenario.
Teacher:

Observation(after unpivot):

Then you may create a measure as follows.

CountID = 
var _currentteacher = SELECTEDVALUE(Teacher[TeacherName])
var _count = 
CALCULATE(
    DISTINCTCOUNT(Observation[ID]),
    Observation[Teacher] = _currentteacher
)
return 
IF(
   _count = BLANK(),
   0,
   _count
)

Result:

Best Regards

Allan

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

[JarroVGIT]

Hi @RossKnorr ,

You indeed need to create a relationship between the column of the first table and the teachername column of the second table. Then , create a table visual and drag first-table teacher name into it, and then the unique ID column from the second table into the visual. Change the summarization method under the list of columns of the visual:

Also check the "Show items with no data" in that menu and you have your solution 🙂 Let me know if this helps. (I assume you don't need help creating relationships, if you do please Google "how to create a relationship between tables power bi" and i am pretty sure the first few hits will explain it in detail :))

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂