Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Count if not in another table

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 b...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

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]
)

Count-if-not-in-another-table

 

 

Best Regards,