Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
we have an employee table and related to this is an access control table with a one to many relationship.
I have a table that shows all the employees that accessed the building by date. count by month.
I need to show in this table, anyone who didnt access the building in that month(worked from home).
e.g
Employee | ||
emploeeID | name | bu |
1 | Joe | Fron Office |
2 | Ross | Banking |
3 | Monica | Treasury |
4 | Rachel | Banking |
5 | Phodea | Treasury |
6 | Chandler | Banking |
access control | |
emploeeID | Event date |
1 | 2021-10-01 |
1 | 2021-10-02 |
1 | 2021-10-03 |
1 | 2021-10-04 |
1 | 2021-10-05 |
1 | 2021-10-06 |
2 | 2021-10-01 |
2 | 2021-10-02 |
2 | 2021-10-03 |
2 | 2021-10-04 |
2 | 2021-10-05 |
2 | 2021-10-06 |
3 | 2021-10-03 |
3 | 2021-10-04 |
3 | 2021-10-05 |
3 | 2021-10-06 |
5 | 2021-10-01 |
5 | 2021-10-02 |
5 | 2021-10-03 |
5 | 2021-10-04 |
5 | 2021-10-05 |
5 | 2021-10-06 |
5 | 2021-10-07 |
6 | 2021-10-05 |
6 | 2021-10-06 |
the result should be:
1 | Joe | Fron Office | 6 |
2 | Ross | Banking | 6 |
3 | Monica | Treasury | 4 |
4 | Rachel | Banking | 0 |
5 | Phodea | Treasury | 7 |
6 | Chandler | Banking | 2 |
Although Rachel never accessd the building and has no records in the access control table, i need to report the fact.
i have tried few things, some DAX. trying to change the table relationships etc
any idea?
thanks a mil guys
Solved! Go to Solution.
Try this code to add a column to the Employee table:
Column =
VAR _A =
CALCULATE(
COUNTROWS( 'access control' ),
'access control'[emploeeID] = EARLIER( Employee[emploeeID] )
)
RETURN
IF( ISBLANK( _A ), 0, _A )
the output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Try this code to add a column to the Employee table:
Column =
VAR _A =
CALCULATE(
COUNTROWS( 'access control' ),
'access control'[emploeeID] = EARLIER( Employee[emploeeID] )
)
RETURN
IF( ISBLANK( _A ), 0, _A )
the output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thanks @VahidDM
the DAX is
Thanks Vahid,
seems to be heading in the right direction.
I do get an issue with EARIER()
any idea why?
thanks a mil again
ps: it needs to be dynamic depending on month selected, think thats why i get the error.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |