Full time vs Part time category
Full time worker means they have worked >= 80% of a work week. A work week is simply 8 hours per day except weekends and holidays (0hrs). Anything less than 80% is Part time.
Work week
I’ve added a work_hours column to the dates table with 8hrs per day except holidays and weekends (0hrs).
Problem
I’m cross checking Cost[rates]. Only billable hours are relevant in this example. The Cost[rates] are calculated by someone else (I think) using sql and referring to the same rates table. I’m assuming they’re correct but trying to verify.
I’ve created a fancy rate lookup similar to @BrianJ solution posted to calculate-wage-cost-with-varying-rates-over-time. Now there is certainly something wrong with my simple Sum_hours measure. Occassionally its doubling or even tripling the hours so that my cost measure is way out. This means the category of parttime vs fulltime is wrong and applies wrong rate. So far I’ve triple checked:
I think what you’re identifying as incorrect duplication is in fact valid data (assuming what has been provided here is valid…).
To test this, I added an index column to the Cost table to separate out each record, and prevent the sum hours measure from rolling up. If we slice down to 7/16/21, id 1309 what you get is this with the index value included in the visual. What you’ll see is that for these criteria there actually are two records (index = 1808 and 1810) with 13 hours, and thus the rollled up sum in your marked up table in the post above is correct.
However, I noticed a major problem in your rate lookup measure. It all looks solid up until the SWITCH(TRUE()) statement. The problem is highlighted below:
This is not a proper statement for evaluation within a SWITCH(TRUE()) measure, though it won’t throw an error. It’s the equivalent of me asking you whether “5 and 32” is true or false? Though the question is logically nonsensical, DAX will always evaluate it as true.
As a test/illustration, I set up the following measure:
Test SwitchTrue =
SWITCH( TRUE(),
3 && 5, 1,
0
)
If you throw that into your visual, you will see that in every circumstance it evaluates to 1 (TRUE). Thus, your rate lookup measure is ALWAYS evaluating to lookup_or_165, even in cases where it should be evaluating the ISBLANK([Sum_hours]) statement.
I hope this is helpful. Full solution file attached.
Thanks Brian! Good reminder for me to look more closely at raw data and index col is useful.
Thanks for the pickup on the switch function. I wrote this to check if both values are not blank. This looks to work for some text fields but not others. Not sure whats going on here but I agree, stay away from relying on this logic. Some other languages like Python are True if value is not None type or (Blank):
This works for some fields like description but not for id. Not sure why:
