Count of occurences

[Jaweed]

Hi all

Need some help.

Table 1  have columns Code, Name

Code Name

01      John   

04      Tom

01      John

03      Robert

04      Tom

04      Tom

Table 2 ( master table) has columns Code and  Name and the relation is 1 to many on Code with table 1

Code  Name

01      John  

02      Alex   

03      Robert 

04      Tom

05      Jack

Can you please help me to calculate the occurence  for all names in Table 2 ( the master list)

Code   Name   Occurence

01 John            2

02 Alex            0

03 Robert        1

04  Tom           3

05  Jack            0

Thanks. 

[fhill · Accepted solution]

If your tables are joined together correctly, it should be as easy as dragging the fact (non-master) 'Name' field to a table on the dim (master) Table...  It should 'Count' by default since these are Text Fields, but if not under 'Values' for the fact (non-master) Name, click the small down arrow and choose 'Count'.

You'll also want to choose the small down arrow on the Dim (Master) name, and choose 'Show Items with no data'

Hope this helps,

FOrrest

[Jaweed]

hi Forrest

many thanks

[EricHulshof]

Create the following measure:

Occurence = CALCULATE(COUNT(Table1[Code]);Table2[Code])+0

The +0 is to make sure the people with 0 occurances show up too.