Count if not in another table

[Qotsa]

Hi,

I have an SQL DB with 2 tables, Staff & Pay.

There is a Many  to one rel-ship based on Staff ID column.

A staff is active or inactive based on their start & end date. If end date is blank then staff is active. If end date is before current day then staff is inactive etc.

Table Pay has a date column that shows every day a staff got paid.

What I need to show is for every Week in Table Pay, the number of staff that were Active in that week but did not get paid i.e they did not work.

I don't even know if this is possible or where to start with this.

[v-jiascu-msft]

Hi @Qotsa,

Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft]

Hi @Qotsa,

Please download a demo from the attachment. If you have a similar scenario, you can try it out.

Measure =
SUMX (
    ADDCOLUMNS (
        Staff,
        "IfNotPaid", IF (
            CALCULATE (
                COUNTROWS ( Pay ),
                FILTER (
                    Pay,
                    Pay[Date] >= MIN ( Staff[Start] )
                        && Pay[Date]
                            <= IF ( ISBLANK ( Staff[End] ), DATE ( 9999, 12, 31 ), MIN ( Staff[End] ) )
                        && Pay[Pay] <> 0
                        && NOT ISBLANK ( Pay[Pay] )
                )
            ) < 5,
            1,
            0
        )
    ),
    [IfNotPaid]
)

Best Regards,

[LivioLanzo]

Hello @Qotsa,

would you mind posting a sample of your data set?

thank you

[Qotsa]

Hi @LivioLanzo,

Thanks for your reply. I'm not sur how I'd even do that except for uploading the whole pbix.

[LivioLanzo]

@Qotsa it is possible to post tabular data which can be copy pasted