Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Qotsa
Helper V
Helper V

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 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.

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi @Qotsa,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

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

Count-if-not-in-another-table

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LivioLanzo
Solution Sage
Solution Sage

Hello @Qotsa,

 

would you mind posting a sample of your data set?

 

thank you

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo,

 

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

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.