Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @Qotsa,
Could you please mark the proper answers as solutions?
Best Regards,
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,
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!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |