Help Required: on conditional counts and Staff Sickness Ratio (Syntax and Dax help)

[JamesLindsay101]

Hi, my request is similar to the one hyperlinked here, however slightly different.

I am looking to form a column of counts in my Date Dimensions table from a table of absences (this table includes a list of absence episodes with columns that include a start date and an end date, and other contextual info that isn't relevant here) how many of these absences include a date from my Date Dimension/bridger table.

My thinking is that if I'm able to count how many times each date in the date column my date dimension table is in between each of the absence start date and end dates, I can work out how many days are being taken off across a specific period and therefore put together a staff sickness ratio measure.

Trying the above hyperlinked solution will only give me if the date dimensions are appearing inbetween the start date and end date, however I'd almost like the reverse of this.

This is generally what I'm trying to do (What I'm aiming towards in DAX):

Total Absences by Day =
CALCULATE(

SUMX(Absences,

IF('Date Dimension'[Date]>=Absences[Absence Start Date]&&'Date Dimension'[Date]<=Absences[Absence End Date])))

I think I'm mixing up measure calculations and column calculations also, Any ideas?

[v-kkf-msft]

Hi @JamesLindsay101 ,

Please try the following measure:

Total Absences by Day = 
SUMX (
    SUMMARIZE (
        'Date Dimension',
        'Date Dimension'[Date],
        "_count",
            CALCULATE (
                COUNTROWS ( Absences ),
                FILTER (
                    Absences,
                    Absences[Absence Start Date] <= MAX ( 'Date Dimension'[Date] )
                        && Absences[Absence End Date] >= MAX ( 'Date Dimension'[Date] )
                )
            )
    ),
    [_count]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JamesLindsay101]

Thanks you, one issue I'm having with this is when I feed this into my sickness ratio calculation and I filter the page by department (to get a departmental view), the department filter seems to be messing this up. I've got a date bridge table (similar to yours above) and a department bridge table. Do you have any possible ideas on how I could make this work?

I think the issue is that the total absences by day column is too disconnected from the departmental relationship(s) in my relationships network

[v-kkf-msft]

Hi @JamesLindsay101 ,

In my model, the Date Dimension table is a separate table and no relationship is established between it and Absences table. So could you please share your model and sample data?

Best Regards,
Winniz

[JamesLindsay101]

Mine currently uses an inactive relationship, is this the same as when there is no relationship?

Please see below the relationship screenshot (I can't share the data as it's sensitive information), note I've made the sub-division (department) dimension bridge table active as it wasn't interacting with the filters on the page when inactive.

The Sickness Ratio measure is made up of the measure in this forum and then 2 headcount measures in the employee listing table. hope this helps