Count of occurrences

[Jaweed]

I have 2 tables tbl1 as master and tbl2, with a relation of 1 to many tbl1 on tbl2 on code.
tbl1

Code. Name

01     John

02     Robert

03      Jill

04      Mary

05      Alex

tbl2

Code. Name

01      John 

01      John

04      Mary

03      Jill

03      Jill

03      Jill

Output required

Code.  Name.  Occurence.   

01.       John.       2

02.       Robert.    0

03.       Jill.           3
04.       Mary.       1

05.       Alex.         0

Can someone please help me? Thanks

[BobBI · Accepted solution]

Hi Jaweed,

Insert a column in table 1 and try this dax.

Hope this would help,

Sukhi

[Jaweed]

@BobBI 

thank you

[tkirilov]

Hi @Jaweed ,

You can create the following measure:

Occurence =
CALCULATE (
COUNTROWS ( tbl2 ),
ALLSELECTED ( tbl2 ),
VALUES ( tbl2[Name] )
)

This is the result I got:

[Jaweed]

@tkirilov 

Thank you. But it does not display the records with zero occurences