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
IanCockcroft
Post Patron
Post Patron

DAX Challenge - count occurence of record

Hi guys,

i am struggling with a piece of DAX.

I have 2 tables. [employee] and[access control]. related on empID

I need a measure tht returns how many times an individual occurs in the [accescontrol] table in a given period. returnig 0 if they dont.

 

eg

employee

employeeIDName
1George
2Lovemore
3Sydey
4Montgomery
5Alecia
6Nadia
7Ramu
8Mark
9Sean
10Travor

 

 

access control

EmpIDEventDate
32021-12-22
12022-01-01
52022-01-02
62022-01-03
82022-01-04
92022-01-05
102022-01-06
102022-01-07
52022-01-08
32022-01-09
12022-01-10
12022-01-11
62022-01-12
42022-01-13
92022-01-14
102022-01-15
92022-01-16
52022-01-17
22022-01-18
22022-01-19
92022-01-20
82022-01-21
62022-01-22
62022-01-23
52022-01-24
42022-01-25
32022-01-26
32022-01-27
42022-01-28
22022-01-29
12022-01-30
12022-01-31

 

filter 1

 2021-12-22 to 2022-01-31
George5
Lovemore3
Sydey4
Montgomery3
Alecia4
Nadia3
Ramu0
Mark2
Sean4
Travor3

 

filter 2

 2022-01-01 to 2022-01-10
George2
Lovemore0
Sydey1
Montgomery0
Alecia2
Nadia1
Ramu0
Mark1
Sean1
Travor2

 

filter 3

 

2022-01-10 to 2022-01-25
George2
Lovemore2
Sydey0
Montgomery2
Alecia2
Nadia3
Ramu0
Mark1
Sean3
Travor1

 

hope thats clear enough?

any ideas

thanks a mil

ian

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture1.png

 

Count access: =
IF( HASONEVALUE( Employee[Name] ),
COUNTROWS('Acess Control' ) + 0
)
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
IanCockcroft
Post Patron
Post Patron

100%

thnks so much

Jihwan_Kim
Super User
Super User

Picture1.png

 

Count access: =
IF( HASONEVALUE( Employee[Name] ),
COUNTROWS('Acess Control' ) + 0
)
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.