Identify records with no information in related table

[Casi]

Hi,

I am producing a report on absences and wonder if anyone can help?

I have the following tables which are joined by 'PersonDetails.ID' and 'Absence.PerID'

Personal Detail

PersonDetails.ID

1

2

3

4

Absence      

Absence.PerID	Absence.ID	Date
1	1	1/05/2022
2	2	02/05/2022
4	3	10/05/2022
4	4	20/05/2022

I need to be able to show staff that havent had an absence within the time period selected. So basically I need it to identify any PersonDetails.ID details that do not appear in the Absence table. 

The only way I can get the data displayed is in a table with filters on but HR have decided they would like pie chart to compare number of no absences against number of absences so I need to come up with a measure? I am struggling everything I have tried is coming up with incorrect information.

Any help would be greatly appreciated.

[hnguy71]

Hi @Casi ,

Your sample includes users that actually are found in your Absent table therefore they all are marked absent. I've included ID #5 to show that he's not absent but you can create a base measure like this:

Not Absent = 
VAR _NotAbsent = CALCULATETABLE(VALUES('Table'[PersonDetails.ID]), ISBLANK(Absence[PersonDetails.ID]))
RETURN
COUNTROWS(_NotAbsent)

Then, you'd create your time intelligence measure on top of it.

[Casi]

Hi. This is marking every member of staff as having no absence. Im not sure how to create the time intelligence? Sorry!

[hnguy71]

Hi @Casi,

I'm assuming you have a relationship between your IDs? You may download a sample here: 

https://1drv.ms/u/s!An8CCFsOzw0uhQkwdIIQEmV3Aqqt?e=MKJyou

[Casi]

Hi @hnguy71 ,

Yes they are in a relationship connected by personal ID

Thank you

[johnt75]

You could try

Num no absences = CALCULATE( COUNTROWS( 'Personal Detail'),
EXCEPT( VALUES('Personal Detail'[PersonDetails.ID]), VALUES('Absence'[Absence.PerID]))
)

[Casi]

Hi,

This is bringing back a result of staff that have never taken an absence rather than within the period selected.

Is there any way I can link to the date of absence? Sorry I am fairly new to PowerBi!

Thank you