Count if not in another table

Question

Hi,&nbsp;I have an SQL DB with 2 tables, Staff &amp; Pay.&nbsp;There is a Many&nbsp; to one rel-ship based on Staff ID column.&nbsp;A staff is active or inactive based on their start &amp; end date. If end date is blank then staff is active. If end date is before current day then staff is inactive etc.&nbsp;Table Pay has a date column that shows every day a staff got paid.&nbsp;What I need to show is for every Week in Table Pay,&nbsp;the number of staff that were Active in that week but did not get paid i.e they did not work.&nbsp;I don't even know if this is possible or where to start with this.

liviolanzo · Answer

Hello&nbsp;Anonymous,&nbsp;would you mind posting a sample of your data set?&nbsp;thank you

anonymous · Answer

Hi&nbsp;LivioLanzo,&nbsp;Thanks for your reply. I'm not sur how I'd even do that except for uploading the whole pbix.

liviolanzo · Answer

Anonymous&nbsp;it is possible to post tabular data which can be copy pasted

v-jiascu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
Please download a demo from the attachment. If you have a similar scenario, you can try it out.
Measure&nbsp;=
SUMX&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Staff,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"IfNotPaid",&nbsp;IF&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS&nbsp;(&nbsp;Pay&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pay,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pay[Date]&nbsp;&gt;=&nbsp;MIN&nbsp;(&nbsp;Staff[Start]&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Pay[Date]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;=&nbsp;IF&nbsp;(&nbsp;ISBLANK&nbsp;(&nbsp;Staff[End]&nbsp;),&nbsp;DATE&nbsp;(&nbsp;9999,&nbsp;12,&nbsp;31&nbsp;),&nbsp;MIN&nbsp;(&nbsp;Staff[End]&nbsp;)&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Pay[Pay]&nbsp;&lt;&gt;&nbsp;0
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;NOT&nbsp;ISBLANK&nbsp;(&nbsp;Pay[Pay]&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&lt;&nbsp;5,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;[IfNotPaid]
)

&nbsp;
&nbsp;
Best Regards,

v-jiascu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
Could you please mark the proper answers as solutions?
&nbsp;
&nbsp;
Best Regards,

Forum Discussion

Count if not in another table

5 Replies

Related content

Count Occurrence of Column Values in Another Table

Counting nulls

How to get Count from two different table- Un-match Count Table Record with New Table

Count distinct value in one table that exists in another table (matched by separate FK)

Power Query LookUp in another table