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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jaweed
Helper III
Helper III

Count of occurrences

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION
BobBI
Resolver III
Resolver III

Hi Jaweed,

 

Insert a column in table 1 and try this dax.

soluton jaweed.PNG

 

Hope this would help,

Sukhi

View solution in original post

4 REPLIES 4
BobBI
Resolver III
Resolver III

Hi Jaweed,

 

Insert a column in table 1 and try this dax.

soluton jaweed.PNG

 

Hope this would help,

Sukhi

@BobBI 

thank you

 

tkirilov
Resolver I
Resolver I

Hi @Jaweed ,

You can create the following measure:

 

Occurence =
CALCULATE (
COUNTROWS ( tbl2 ),
ALLSELECTED ( tbl2 ),
VALUES ( tbl2[Name] )
)
 
This is the result I got:
 
 

Occurence.PNG

@tkirilov 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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